UNION tables with wildcard in BigQuery

user_16110549 0
Viewed 464

I have over 40 tables I want to append in BigQuery using standard SQL. I have already formatted them to have the exact same schema. When I try to use the '*' wildcard at the end of table name in my FROM clause, I get the following error:

Syntax error: Expected end of input but got "*" at [95:48]

I ended up manually doing a UNION DISTINCT on all my tables. Is this the best way to do this? Any help would be appreciated. Thank you!

CREATE TABLE capstone-320521.Trips.Divvy_Trips_All AS 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_*;



--JOIN all 2020-21 trips data
CREATE TABLE capstone-320521.Trips.Divvy_Trips_Raw_2020_2021 AS 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_04  
UNION DISTINCT 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_05  
UNION DISTINCT 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_06 
UNION DISTINCT
google-bigquery wildcard sql
2 Answers

Syntax error: Expected end of input but got "*"

I think the problem is in missing ticks around the table references. Try below

CREATE TABLE `capstone-320521.Trips.Divvy_Trips_All` AS 
SELECT * FROM `capstone-320521.Trips.Divvy_Trips_*`

Note: The wildcard table name contains the special character (*), which means that you must enclose the wildcard table name in backtick (`) characters. See more at Enclose table names with wildcards in backticks

user_52219440

I am unaware of any such UNION DISTINCT syntax. If your intention is to do a union of the 3 tables and remove any duplicate records in the process, then just using UNION should suffice:

CREATE TABLE capstone-320521.Trips.Divvy_Trips_Raw_2020_2021 AS
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_04
UNION
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_05
UNION
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_06;

Note that in general it is bad practice to use SELECT * with union queries. The reason has to do with that a union between two (or more) tables is generally only valid if the two queries involved have the number and types of columns. Using SELECT * obfuscates what columns are actually being selected, and so it is preferable to always explicitly list out the columns.

user_18632290
Related