GCP Data Engineer-- (BigQuery 8) Session 41 Practicals on Partitioning Clustering on Gcloudsdk prom

BigQuery Tables with Partitioning and Clustering

The session demonstrates how to create BigQuery tables with partitioning and clustering using the Google Cloud console and Cloud Shell, then load data into them.

Table-creation approaches

The instructor reviews six ways to create tables:

1. BigQuery console UI: Create a table through the interface. This approach may limit control over the partitioning column, such as when using ingestion-time partitioning.
2. Console with a LOAD DATA command: Create a table and load data in one operation.
3. Console with separate commands: Create the table first, then load the data.
4. Cloud Shell with bq query: Run a SQL CREATE TABLE statement with --use_legacy_sql=false, then load data separately.
5. Cloud Shell with bq mk -t and an inline schema: Specify column names and types in the command, along with partitioning and clustering options.
6. Cloud Shell with bq mk -t and a JSON schema file: Store the table schema in JSON and pass the file to the command. Partitioning and clustering options can be supplied as with the inline-schema approach.

Projects may use different approaches. For interviews and project work, learners should understand the options and be prepared to explain a specific approach rather than simply list them.

Cloud Shell workflow

• Open Google Cloud Shell from the Google Cloud console.
• To create a table from SQL, run the statement with bq query and set --use_legacy_sql=false.
• To load a CSV, use bq load with the source format, destination table, and Cloud Storage file path. Skip a header row when appropriate with the equivalent of --skip_leading_rows=1.
• Substitute the correct project, dataset, table, and file path. Example commands will fail if those names are copied unchanged.
• To create a JSON schema file in Cloud Shell, use cat with output redirection, then press Ctrl+D to finish entering it. Use pwd, ls, and cat to check the file’s path, presence, and contents. Files created in Cloud Shell are not automatically stored on the local computer.

Partitioning and clustering

• BigQuery supports time-based partitioning on date or timestamp fields and range partitioning on integer fields.
• For time partitioning, specify a field such as a hiring-date column.
• For integer range partitioning, specify the column and the range’s start, end, and interval.
• The examples also use a required partition filter and cluster the table by a country field.
• After loading data, inspect partition information with the partition-list query covered in an earlier session.

Troubleshooting

• Check the project, dataset, table, schema-file path, and Cloud Storage path before running commands.
• Use Google Cloud Shell for the demonstrated commands. Running them in a separately installed Cloud SDK environment may require different handling.
• If a CSV load fails, check the header-row skip setting and confirm that date values use a format BigQuery accepts. The class example uses YYYY-MM-DD.
• An extra line or malformed entry in the JSON schema can cause table-creation errors. Inspect and correct the file.
• To view command output in Cloud Shell, run a query through bq query.

Speaker and source

Main speaker/source: The course instructor from EDUCATION MACHINE. Students ask questions and report command or data-loading issues during the demonstrations.