gcp-bq-table-management（gcp bq 表管理）

BigQuery Table Management

Use this skill when creating, modifying, or optimizing BigQuery table structures with partitioning and clustering.

Creating Tables

Basic Table Creation

Using bq mk:

bq mk -t \
  --schema 'customer_id:STRING,amount:FLOAT,date:DATE' \
  --description "Customer orders table" \
  project:dataset.orders

Using SQL DDL:

CREATE TABLE `project.dataset.orders` (
  customer_id STRING,
  amount FLOAT64,
  date DATE,
  created_at TIMESTAMP
);

Partitioning Strategies

Time-Based Partitioning

Create time-partitioned table:

bq mk -t \
  --schema 'timestamp:TIMESTAMP,customer_id:STRING,amount:FLOAT' \
  --time_partitioning_field timestamp \
  --time_partitioning_type DAY \
  project:dataset.orders

SQL DDL version:

CREATE TABLE `project.dataset.orders` (
  timestamp TIMESTAMP,
  customer_id STRING,
  amount FLOAT64
)
PARTITION BY DATE(timestamp);

Partitioning options:

• DAY - Daily partitions (most common)
• HOUR - Hourly partitions (for high-volume data)
• MONTH - Monthly partitions (for historical data)
• YEAR - Yearly partitions (for very old data)

Ingestion-Time Partitioning

Create table with automatic _PARTITIONTIME:

bq mk -t \
  --schema 'customer_id:STRING,amount:FLOAT' \
  --time_partitioning_type DAY \
  project:dataset.orders

Query with ingestion-time partition:

SELECT * FROM `project.dataset.orders`
WHERE _PARTITIONTIME >= '2024-01-01'

Range Partitioning

Create range-partitioned table:

bq mk -t \
  --schema 'customer_id:INTEGER,region:STRING,sales:FLOAT' \
  --range_partitioning=customer_id,0,100,10 \
  project:dataset.sales

Parameters: field,start,end,interval

• Creates partitions: [0,10), [10,20), [20,30),..., [90,100)

SQL DDL version:

CREATE TABLE `project.dataset.sales` (
  customer_id INT64,
  region STRING,
  sales FLOAT64
)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10));

Clustering

Basic Clustering

Create clustered table:

bq mk -t \
  --schema 'timestamp:TIMESTAMP,customer_id:STRING,product_id:STRING,amount:FLOAT' \
  --clustering_fields customer_id,product_id \
  project:dataset.orders

SQL DDL version:

CREATE TABLE `project.dataset.orders` (
  timestamp TIMESTAMP,
  customer_id STRING,
  product_id STRING,
  amount FLOAT64
)
CLUSTER BY customer_id, product_id;

Clustering rules:

• Up to 4 clustering columns
• Order matters (first column has most impact)
• Works best with WHERE, GROUP BY, JOIN filters

Partitioning + Clustering (Recommended)

Combined approach:

bq mk -t \
  --schema 'timestamp:TIMESTAMP,customer_id:STRING,transaction_amount:FLOAT' \
  --time_partitioning_field timestamp \
  --clustering_fields customer_id \
  --description "Partitioned by day, clustered by customer" \
  project:dataset.transactions

SQL DDL version:

CREATE TABLE `project.dataset.transactions` (
  timestamp TIMESTAMP,
  customer_id STRING,
  transaction_amount FLOAT64
)
PARTITION BY DATE(timestamp)
CLUSTER BY customer_id;

Query benefits:

-- Partition pruning + clustering optimization
SELECT * FROM `project.dataset.transactions`
WHERE DATE(timestamp) = '2024-01-15'  -- Partition filter
AND customer_id = 'CUST123'            -- Cluster filter

Table Configuration Options

Expiration

Set table expiration:

bq mk -t \
  --expiration 2592000 \
  --schema 'field:TYPE' \
  project:dataset.temp_table

Expiration in seconds: 2592000 = 30 days

Update existing table:

bq update --expiration 604800 project:dataset.table

Remove expiration:

bq update --expiration 0 project:dataset.table

Labels

Add labels:

bq mk -t \
  --schema 'field:TYPE' \
  --label environment:production \
  --label team:analytics \
  project:dataset.table

Update labels:

bq update --set_label environment:staging project:dataset.table

Description

Set description:

bq update \
  --description "Customer transaction history with daily partitioning" \
  project:dataset.transactions

Schema Management

Adding Columns

Cannot add required columns to existing data:

# Add optional column
bq query --use_legacy_sql=false \
  'ALTER TABLE `project.dataset.table`
   ADD COLUMN new_field STRING'

Changing Column Modes

REQUIRED → NULLABLE (allowed):

ALTER TABLE `project.dataset.table`
ALTER COLUMN field_name DROP NOT NULL;

NULLABLE → REQUIRED (NOT allowed if data exists)

Relaxing Column Types

Allowed type changes:

• INT64 → FLOAT64 ✅
• INT64 → NUMERIC ✅
• INT64 → BIGNUMERIC ✅
• INT64 → STRING ✅

Example:

ALTER TABLE `project.dataset.table`
ALTER COLUMN amount SET DATA TYPE FLOAT64;

External Tables

Create External Table (GCS)

CSV in GCS:

bq mk \
  --external_table_definition=gs://bucket/*.csv@CSV \
  --schema='customer_id:STRING,amount:FLOAT' \
  project:dataset.external_orders

Parquet in GCS (schema auto-detected):

bq mk \
  --external_table_definition=gs://bucket/*.parquet@PARQUET \
  project:dataset.external_data

Supported formats: CSV, JSON, AVRO, PARQUET, ORC

External Table Limitations

• No DML operations (INSERT, UPDATE, DELETE)
• No guaranteed performance SLAs
• Data must be in GCS, Drive, or Bigtable
• Cannot be partitioned (but can use hive partitioning)

Snapshots and Clones

Table Snapshots

Create snapshot:

CREATE SNAPSHOT TABLE `project.dataset.orders_snapshot`
CLONE `project.dataset.orders`;

Restore from snapshot:

CREATE OR REPLACE TABLE `project.dataset.orders`
CLONE `project.dataset.orders_snapshot`;

Snapshot retention: 7 days by default

Table Clones

Create table clone:

CREATE TABLE `project.dataset.orders_clone`
CLONE `project.dataset.orders`;

Difference from snapshot:

• Clone = new independent table
• Snapshot = point-in-time reference

Time Travel

Query historical data:

-- Query table as it was 1 hour ago
SELECT * FROM `project.dataset.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

-- Query table at specific time
SELECT * FROM `project.dataset.orders`
FOR SYSTEM_TIME AS OF '2024-01-15 10:00:00 UTC';

Time travel window: 7 days (168 hours) by default

Best Practices

Partition Selection

Use time-based partitioning when:

• Data has timestamp/date column
• Queries filter by time ranges
• Data arrives chronologically
• Want automatic partition management

Use range partitioning when:

• Partitioning on integer column (ID, age, etc.)
• Predictable value distribution
• Fixed range boundaries

Use ingestion-time partitioning when:

• No natural timestamp column
• Loading data from streaming sources
• Want simple partition management

Clustering Selection

Cluster on columns that are:

• Frequently used in WHERE clauses
• Used in JOIN conditions
• Used in GROUP BY
• High cardinality (many distinct values)

Order matters:

• Most filtered column first
• Then second most filtered
• Up to 4 columns total

Partitioning + Clustering Strategy

Optimal pattern:

CREATE TABLE `project.dataset.optimized` (
  event_timestamp TIMESTAMP,      -- Partition on this
  customer_id STRING,              -- Cluster on this (1st)
  product_category STRING,         -- Cluster on this (2nd)
  amount FLOAT64
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, product_category;

Query pattern:

-- Both partition and cluster benefit
SELECT SUM(amount)
FROM `project.dataset.optimized`
WHERE DATE(event_timestamp) BETWEEN '2024-01-01' AND '2024-01-31'
AND customer_id = 'CUST123'
GROUP BY product_category;

Checking Table Metadata

Get table information:

bq show --format=prettyjson project:dataset.table

Check partition info:

SELECT
  partition_id,
  total_rows,
  total_logical_bytes,
  last_modified_time
FROM `project.dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'orders'
ORDER BY partition_id DESC
LIMIT 10;

Check clustering info:

SELECT
  table_name,
  clustering_ordinal_position,
  column_name
FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE clustering_ordinal_position IS NOT NULL
ORDER BY table_name, clustering_ordinal_position;

Common Pitfalls

❌ Too many partitions

Problem: Creating 100,000+ partitions Limit: 10,000 partitions per table Solution: Use larger partition granularity (MONTH vs DAY)

❌ Wrong partition column

Problem: Partitioning on column not used in queries Solution: Partition on columns used in WHERE clauses

❌ Not filtering on partition

Problem: Query scans all partitions Solution: Always include partition filter in WHERE

❌ Clustering too many columns

Problem: Clustering on 5+ columns Limit: 4 columns maximum Solution: Choose most selective columns

❌ Wrong cluster order

Problem: Least selective column first Solution: Put most selective column first

Table Maintenance

Update partition expiration

Set partition expiration:

bq update \
  --time_partitioning_expiration 2592000 \
  project:dataset.partitioned_table

Query shows this: Partitions older than 30 days auto-delete

Optimize table storage

Run optimization query:

-- BigQuery automatically optimizes storage
-- No manual VACUUM or OPTIMIZE needed

BigQuery automatically:

• Compacts data
• Sorts by clustering columns
• Removes deleted rows
• Optimizes storage format

Access Control & Security

Row-Level Security

Row-level access policies filter data based on user/group membership. They coexist with column-level security.

Creating row-level policies:

CREATE ROW ACCESS POLICY policy_name
ON dataset.table
GRANT TO ("user:[email protected]")
FILTER USING (region = "US");

Multiple policies:

-- Policy for US users
CREATE ROW ACCESS POLICY us_users_policy
ON dataset.orders
GRANT TO ("group:[email protected]")
FILTER USING (region = "US");

-- Policy for managers (see all regions)
CREATE ROW ACCESS POLICY managers_policy
ON dataset.orders
GRANT TO ("group:[email protected]")
FILTER USING (TRUE);

Viewing policies:

SELECT * FROM dataset.INFORMATION_SCHEMA.ROW_ACCESS_POLICIES
WHERE table_name = 'orders';

Dropping policies:

DROP ROW ACCESS POLICY policy_name ON dataset.table;

Column-Level Security

Use policy tags from Data Catalog to restrict access to sensitive columns:

Creating table with policy tags:

CREATE TABLE dataset.customers (
  customer_id STRING,
  name STRING,
  email STRING,
  ssn STRING OPTIONS(
    policy_tags=("projects/PROJECT/locations/LOCATION/taxonomies/TAXONOMY/policyTags/PII_TAG")
  ),
  credit_score INT64 OPTIONS(
    policy_tags=("projects/PROJECT/locations/LOCATION/taxonomies/TAXONOMY/policyTags/SENSITIVE_TAG")
  )
);

Adding policy tags to existing columns:

ALTER TABLE dataset.customers
ALTER COLUMN ssn SET OPTIONS(
  policy_tags=("projects/PROJECT/locations/LOCATION/taxonomies/TAXONOMY/policyTags/PII_TAG")
);

How it works:

1. Create taxonomy and policy tags in Data Catalog
2. Apply policy tags to table columns
3. Grant IAM roles on policy tags (datacatalog.categoryFineGrainedReader)
4. Users without permission cannot query those columns

Authorized Views

Views that allow users to query data without direct table access:

Use cases:

• Sharing specific columns/rows without full table access
• Implementing business logic in access control
• Best performance for row/column filtering

Setup process:

-- 1. Create view in dataset A
CREATE VIEW datasetA.public_orders AS
SELECT order_id, customer_id, amount, order_date
FROM datasetA.orders
WHERE status = 'completed';

-- 2. Grant dataset B's view access to dataset A's table
-- This is done via dataset permissions in Cloud Console or:
bq update --source datasetA.orders \
  --view datasetB.public_view

Example authorized view:

-- View in shared_views dataset
CREATE VIEW shared_views.customer_summary AS
SELECT
  customer_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spent
FROM private_data.orders
GROUP BY customer_id;

-- Grant access to view (not underlying table)
-- Users can query shared_views.customer_summary
-- but cannot access private_data.orders

Benefits:

• Row/column filtering without policy overhead
• Business logic in SQL (e.g., only show completed orders)
• Best query performance
• Centralized access control

Security Best Practices

1. Layered security:

• Use row-level policies for user-based filtering
• Use column-level security for sensitive data (PII, PHI)
• Use authorized views for complex access patterns

2. Performance:

• Authorized views: Best performance (compiled into query)
• Row-level policies: Slight overhead (filter applied)
• Column-level: No performance impact

3. Combining approaches:

-- Table with column-level security AND row-level policy
CREATE TABLE dataset.sensitive_data (
  user_id STRING,
  region STRING,
  ssn STRING OPTIONS(policy_tags=("...")),
  data JSON
)
PARTITION BY DATE(created_at);

-- Row-level policy
CREATE ROW ACCESS POLICY regional_access
ON dataset.sensitive_data
GRANT TO ("group:[email protected]")
FILTER USING (region = "US");

4. Auditing: Monitor access with Cloud Audit Logs:

SELECT
  timestamp,
  principal_email,
  resource_name,
  method_name
FROM `PROJECT.DATASET.cloudaudit_googleapis_com_data_access_*`
WHERE resource.type = "bigquery_dataset"
ORDER BY timestamp DESC;

Quick Reference

Partition types:

• Time-based: HOUR, DAY, MONTH, YEAR
• Ingestion-time: Automatic _PARTITIONTIME
• Range: Integer column ranges

Clustering:

• Max 4 columns
• Order matters
• Works with or without partitioning

Security:

• Row-level: Filter by user/group
• Column-level: Policy tags for sensitive data
• Authorized views: Business logic filtering

Limits:

• 10,000 partitions per table
• 4 clustering columns
• 7-day time travel window
• 10,000 columns per table
• 100 row-level policies per table