Bigquery and Google Cloud Storage

Question

I am trying to use bigquery to query data from google cloud storage.

Those are the data for my real time DB in firebase. It consists of json file. How can I query through and see data inside each file?

I am actually reading through this but I don't understand how to connect.

https://cloud.google.com/bigquery/external-data-cloud-storage

Update

If I point to particular file (like firebase.json), it is okay. But i need to update my data.

Answer 1

Using UI by 2018-10-29

If you want to do it from the UI. Go to bigquery, at the left click on your

project -> dataset -> at the right click on cleater Table

Fill the rest of the information

• Create table from: Google Cloud Storage
• Select from GCS bucket: path to your database location on GCS
• File format: JSON
• Under the schema section click auto detect schema

That should do the work using the new UI by 2018-10-29

Using CLI

You can do it from google console as well using bigquery CLI like

bq mk --external_table_definition=gs://yourbucket/path/yourdb --destination_format NEWLINE_DELIMITED_JSON --autodetect yourdataset.yourtablename

Last arguments is your dataset name dot your table name.

You don't need to specify a file, you can point to a folder. About compression, from BigQuery documentation

If you use gzip compression BigQuery cannot read the data in parallel. Loading compressed JSON data into BigQuery is slower than loading uncompressed data.

So it is supported, but not recommended from a performance point of view due the limitations of the gz format, Anyway if should have several small files, it shouldn't matter, I think it is more related to big files gziped.

EDIT: If you need to point multiple files, you can use wildcard like --external_table_definition=gs://yourbucket/path/yourdb/*