ListJoinPatterns

ListJoinPatterns lists how other SQL joins a table, or one of its columns: every join key that the SQL of a table, view or model in the workspace uses to join the requested entity, read from the requested entity’s side, and which SQL makes it. It is the reverse of BatchSqlDetails joins, which say how building one entity joins others: here the requested entity is the one being joined.

The requested entity is taken together with every entity that is the same object, like a dbt model, the table it builds and that table in a catalog, so asking about any of them returns the joins on all of them.

GET /api/sql-insights/v1/join-patterns

ListJoinPatterns

cURL

curl --request GET \
  --url https://developer.synq.io/api/sql-insights/v1/join-patterns \
  --header 'Authorization: Bearer <token>'

200

{
  "patterns": [
    {
      "key": [
        {
          "column": {
            "entityId": "<string>",
            "column": "<string>",
            "derivation": "JOIN_SIDE_DERIVATION_UNSPECIFIED",
            "expression": "<string>"
          },
          "other": {
            "entityId": "<string>",
            "column": "<string>",
            "derivation": "JOIN_SIDE_DERIVATION_UNSPECIFIED",
            "expression": "<string>"
          },
          "kind": "JOIN_KIND_UNSPECIFIED",
          "operator": "JOIN_OPERATOR_UNSPECIFIED",
          "leads": true,
          "joinType": "JOIN_TYPE_UNSPECIFIED",
          "nativeJoinType": "<string>"
        }
      ],
      "filters": [
        "<string>"
      ],
      "evidenceKinds": [
        "JOIN_EVIDENCE_KIND_UNSPECIFIED"
      ],
      "sources": [
        {
          "entityId": "<string>",
          "statementIndex": 123,
          "joinIndex": 123
        }
      ],
      "queryLog": {
        "queries": 123,
        "distinctLogins": 123,
        "firstHour": "2023-01-15T01:30:15.01Z",
        "lastHour": "2023-01-15T01:30:15.01Z",
        "issuers": [
          {
            "platform": "<string>",
            "entityId": "<string>",
            "queries": 123,
            "distinctLogins": 123,
            "kind": "JOIN_ISSUER_KIND_UNSPECIFIED"
          }
        ],
        "sampleQueryId": "<string>"
      }
    }
  ],
  "queryLogCoveredHours": 123,
  "queryLogLeftOut": {
    "coalesceQualityQueries": 123,
    "coalesceLogins": 123,
    "serviceAccounts": 123
  }
}

Authorizations

Authorization · string · header · required

Bearer authentication header of the form Bearer <token>, where <token> is your auth token.

Query Parameters

entityId · string

The synq path of the table, view or model to list the join patterns of, as the paths of BatchSqlDetailsRequest take it.

Required string length: 1 - 4096

column · string

Set to return only the patterns whose key compares this column of the entity, like customer_id. Matched case-insensitively. Leave empty for every column.

Maximum string length: 4096

otherEntityId · string

Set to return only how the entity is joined with this other entity, which answers "how do I join these two tables". A key that also compares a third table keeps only its comparisons with this one. Like entity_id, it matches every entity that is the same object. Leave empty for joins with any entity.

Maximum string length: 4096

queryLog.from.seconds · integer<int64> string<int64>

Represents seconds of UTC time since Unix epoch 1970-01-01T00:00:00Z. Must be from 0001-01-01T00:00:00Z to 9999-12-31T23:59:59Z inclusive.

queryLog.from.nanos · integer<int32>

Non-negative fractions of a second at nanosecond resolution. Negative second values with fractions must still have non-negative nanos values that count forward in time. Must be from 0 to 999,999,999 inclusive.

queryLog.minQueries · integer<int64> string<int64>

A pattern seen only in the query history is listed when it reaches any of these thresholds that is set. 0 sets none, and with none set every pattern is listed. A pattern that a SQL definition makes is always listed.

queryLog.minLogins · integer

queryLog.includeCoalesceQuality · boolean

Set to keep the queries Coalesce ran: those Coalesce Quality's tag marks, and those of the warehouse users Coalesce connects as. Leave unset to leave them out, as popularity does, and count them in query_log_left_out.

queryLog.excludeServiceAccounts · boolean

Set to leave out the queries of every warehouse user in a service account, and count them in query_log_left_out.

bareOnly · boolean

Set to return only the patterns whose every side is a plain column, with no expression around it, like lower(email) or a cast.

Response

200 - application/json

Success

ListJoinPatternsResponse lists the join patterns of the requested entity.

patterns · JoinPattern · object[]

The join patterns. Those that a SQL definition makes come first, then those of the query history only, by distinct logins, the last hour seen and queries. Empty when no SQL in the workspace joins the entity, or none was parsed yet.

Show child attributes

queryLogCoveredHours · integer

How many hours of the query_log window the query history covered in full. 0 with no QUERY_LOG pattern means that no query history was read for the window, not that no query made a join.

queryLogLeftOut · JoinQueryLogLeftOut · object

The queries left out of the query history evidence. Set when the request sets query_log.

Show child attributes