CODEWITHSUNDEEP
google-bigquery
RKD314
RKD314

Reputation: 1185

BigQuery standard SQL table wildcards with date columns

I'm getting an error when using a date column in the BigQuery web UI, with a query in standard SQL, when using table wildcards.

This works:

#standardSQL
SELECT 
  sale_item,
  date_of_sale
FROM `my-project.past_sales.sales_20170601`
limit 100

This does not:

#standardSQL
SELECT 
  sale_item,
  date_of_sale
FROM `my-project.past_sales.sales_*`
WHERE _TABLE_SUFFIX BETWEEN "20170530" AND "20170601"
limit 100

The column date_of_sale is type DATE, while sale_item is type STRING. The error is: Error: Invalid adaptation for field: date_of_sale (message Msg_0_CLOUD_QUERY_TABLE). Cannot change from TYPE_STRING to int32

Upvotes: 0

Views: 1192

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

This means that date_of_sale is a DATE in one table and a STRING in another. You can find the culprit by taking a union instead:

#standardSQL
SELECT * FROM `my-project.past_sales.sales_20170530` UNION ALL
SELECT * FROM `my-project.past_sales.sales_20170531` UNION ALL
SELECT * FROM `my-project.past_sales.sales_20170601`;

You don't actually need to run the query; the validator in the UI will indicate where the mismatched column type is.

To fix the error, you can either regenerate the data for the problematic day, applying a cast, or you can run a query with a union instead where you add an explicit cast. For example, supposing that the table for 20170531 has the column as a STRING, you could do:

#standardSQL
SELECT 
  sale_item,
  date_of_sale
FROM (
  SELECT *
  FROM `my-project.past_sales.sales_*`
  WHERE _TABLE_SUFFIX BETWEEN "20170530" AND "20170601" AND
    _TABLE_SUFFIX != 20170531
) UNION ALL
SELECT * REPLACE(CAST(date_of_sale AS DATE) AS date_of_sale)
FROM `my-project.past_sales.sales_20170531`
LIMIT 100;

Upvotes: 3

Related Questions