CODEWITHSUNDEEP
google-bigquery
Joel
Joel

Reputation: 487

Load data from csv in google cloud storage as bigquery 'in' query

I want to compose such query using bigquery, my file stored in Google cloud platform storage: 

select * from my_table where id in ('gs://bucket_name/file_name.csv')

I get no results. Is it possible? or am I missing something?

Upvotes: 0

Views: 143

Answers (1)

Pentium10
Pentium10

Reputation: 207912

You are able using the CLI or API to do adhoc queries to GCS files without creating tables, a full example is covered here Accessing external (federated) data sources with BigQuery’s data access layer

code snippet is here:

BigQuery query --external_table_definition=healthwatch::date:DATETIME,bpm:INTEGER,sleep:STRING,type:STRING@CSV=gs://healthwatch2/healthwatchdetail*.csv 'SELECT date,bpm,type FROM healthwatch WHERE type = "elevated" and bpm > 150;'

Waiting on BigQueryjob_r5770d3fba8d81732_00000162ad25a6b8_1 ... (0s) 
Current status: DONE   
+---------------------+-----+----------+
|        date    | bpm | type   |
+---------------------+-----+----------+
| 2018-02-07T11:14:44 | 186 | elevated |
| 2018-02-07T11:14:49 | 184 | elevated |
+---------------------+-----+----------+

on other hand you can create a permament EXTERNAL table with autodetect schema to facilitate WebUI and persistence read more about that here Querying Cloud Storage Data

Upvotes: 2

Related Questions