CODEWITHSUNDEEP
google-bigquery
DoiT International
DoiT International

Reputation: 2435

JOIN Query Syntax with Google BigQuery

I am trying to run a query as following and it seems not being supported in BigQuery. 

SELECT lat, long, spot, spotMAC FROM [data.reports] t1
JOIN [data.operators] t2 
ON t1.spot like CONCAT('%',t2.name,'%')

Is there another way to form the query?

Upvotes: 0

Views: 507

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

another option:

below should give you an idea (and starting point if you will like it)
idea here is to enrich [data.reports] table with extra field that than can be used as a join criteria

SELECT spot, match FROM JS(
(
  SELECT spot, pat
  FROM (
    SELECT spot FROM [data.reports]
  ) AS t1
  CROSS JOIN (
    SELECT GROUP_CONCAT(UNIQUE(name), '|') AS pat FROM [data.operators]
  ) AS t2
) ,
spot, pat,
"[{name: 'spot', type:'string'},
  {name: 'match', type:'string'}]
",
"function(r, emit) {
  var re = new RegExp(r.pat, 'gi');
  var matches = r.spot.match(re);
  if (matches) {
    for (j=0; j < matches.length; ++j) {
      emit({spot: r.spot, match: matches[j]});
    }
  }
}"
)

now you have match field along with spot, you can do JOIN ON xx.match = yy.name
it is not clear which field in your outer select comes from which table, so i am leaving it here
hope you will complete this on your own

below is for test: 

SELECT spot, pat, match FROM JS(
(
  SELECT spot, pat
  FROM (
    SELECT spot FROM 
    (SELECT '11aaa22ccc99' AS spot),
    (SELECT '33aaa44' AS spot),
    (SELECT '55bbb66' AS spot),
    (SELECT '77ddd88' AS spot),
  ) AS t1
  CROSS JOIN (
    SELECT GROUP_CONCAT(UNIQUE(name), '|') AS pat FROM 
    (SELECT 'aaa' AS name),
    (SELECT 'bbb' AS name),
    (SELECT 'ccc' AS name),
  ) AS t2
) ,
spot, pat,
"[{name: 'spot', type:'string'},
  {name: 'pat', type:'string'},
  {name: 'match', type:'string'}]
",
"function(r, emit) {
  var re = new RegExp(r.pat, 'gi');
  var matches = r.spot.match(re);
  if (matches) {
    for (j=0; j < matches.length; ++j) {
      emit({spot: r.spot, pat: r.pat, match: matches[j]});
    }
  }
}"
)

Upvotes: 0

user6116741
user6116741

Reputation:

To implement what you need, you could try something like:

SELECT ...
FROM [Data.reports] t1
INNER JOIN [data.operators] t2
ON t1.<some field>=t2.<some field>
WHERE t2.name CONTAINS t1.spot

This should work if you could find some field on which you can join (it doesn't need to be a precise joining condition - it just needs to include all records that would match the t2.name CONTAINS t1.spot, which you apply further on to get the precise joining condition you need).

If such a JOIN field can't be found, you could try a CROSS JOIN (which doesn't require ON clause):

SELECT ...
FROM [data.reports] t1
CROSS JOIN [data.operators] t2
WHERE t2.name CONTAINS t1.spot

A query with CROSS JOIN will however succeed only for reasonably sized tables.

Upvotes: 1

Related Questions