BigQuery

Question 1

BigQuery description

Answer 1

petabyte scale analytics database service for data warehousing

Question 2

BigQuery key points (9)

Answer 2

1. serverless
2. uses standard sql queries
3. near real-time interactive analysis of massive data sets
4. can access info stored in Cloud Storage, Cloud SQL, Bigtable and Google Drive
5. Storage and computing are handled and billed separately
6. Automatic data replication
7. Can modify data with DLL
8. Can query public or commercial data sets.
9. High availability

Question 3

What are 3 BigQuery use cases?

Answer 3

1. Real-time Inventory
2. Predictive Marketing
3. Analytical Events

Question 4

What Google apps can BigQuery access?

Answer 4

1. Cloud Storage
2. Cloud SQL
3. Cloud Bigtable
4. Google Drive

Question 5

How do you estimate the cost of a BigQuery query via shell?

Answer 5

You run the query with a flag of –dry_run set

bq –location=[LOCATION] query –use_legacy_sql=false –dry_run [SQL QUERY]

Question 6

What are jobs in BigQuery?

Answer 6

Processes used to load, export copy and query data. Jobs are automatically started when you start one of these processes.

Question 7

How do you view the status of a BigQuery job? (Shell and console)

Answer 7

Console - click job history from BQ console

Shell - bq –location=[LOCATION] show -j [JOB ID]

Question 8

How do you export BQ data from the console?

Answer 8

go to BigQuery -> Resources, open the dataset containing the table to be exported and select the table. Export options are on upper right

Question 9

Where can you export BQ data to?

Answer 9

Cloud Storage or Data Studio (a GCP analysis tool)

Question 10

How do you import BQ data via console?

Answer 10

go to BiqQuery -> Resources and select a dataset to import into.
Click create table tab.
Select a source,
file format (if source is not empty table)
table type (external or native - if external, data is kept in source location and only metdata about the table is stored in BigQuery
table name

Question 11

What file formats can you import data from in BigQuery?

Answer 11

CSV
JSON
Avro
Parquet
PRC
Cloud Datastore Backup

Question 12

How do you export BigQuery data from the command line?

Answer 12

bq extract –destination_format [FORMAT] –compression [COMPRESSION] –field_delimiter [DELIMITER] –print_header {BOOLEAN] [PROJECT ID]:[DATASET].[TABLE] gs://[BUCKET]/FILENAME

Question 13

How do you import data into BigQuery from the command line?

Answer 13

bq load –autodetect –source_format=[FORMAT[ [DATASET].[TABLE] [PATH to SOURCE]