BigQuery Migration Metadata Extraction
Extraction runs the DWH migration extractor (dwh-migration-dumper) through two
gcloud commands to collect catalog DDL and query history—not table row data. If
the user wants to migrate table rows (such as Snowflake COPY INTO unloads),
ask whether they prefer the BigQuery Data Transfer Service (including
the Snowflake connector) or exporting to Cloud Storage and
loading with bq load. Both extract commands are in the alpha release
track:
| Command |
Use for |
gcloud alpha bq migration metadata extract |
Catalog metadata (metadata connectors) |
gcloud alpha bq migration query-logs extract |
Query history (*-logs connectors) |
Both take the same arguments. gcloud flags come first; the extractor's own flags
come after -- and are passed to the extractor unchanged:
gcloud alpha bq migration {metadata|query-logs} extract \
--connector=CONNECTOR --output=gs://BUCKET/PATH/ \
[--run-mode=local|cloud] [cloud flags] \
-- [EXTRACTOR FLAGS] [PASSWORD]
--connector and --output are required gcloud flags. --output is a
Cloud Storage path (gs://...); local mode also accepts a local file or
directory path. This skill uses Cloud Storage in both run modes.
--run-mode=local (the default) runs the extractor on this machine.
--run-mode=cloud runs it on a temporary Compute Engine VM (the BigQuery
Migration Cloud Extractor).
- Database flags (
--host, --port, --user, --database, --schema,
--warehouse, --role, --driver, --assessment, --query-log-*, ...)
go after --. Putting them before -- is an error.
- How the password is passed depends on the run mode (Section 4): an extractor
response file (
@FILE) in local mode, and --password read from a file in
cloud mode. Never type a literal password.
[!IMPORTANT]
The commands are in the alpha track. If gcloud reports an invalid choice or
unknown command, ask the user before updating the Google Cloud CLI, then
retry. Update it with gcloud components update, or, if gcloud was installed
with a system package manager such as apt or yum (which disables the
component manager), upgrade the google-cloud-cli package with that package
manager.
Usage Attribution
MUST READ: Before executing any gcloud, REST API, or MCP command, you
MUST read and follow the instructions in
Usage Attribution.
Before you begin
Google Cloud CLI, authentication, project, region, and zone: Ensure the
Google Cloud CLI is installed, verify that the Google
Cloud project has an active Cloud Billing account, authenticate with user
and Application Default Credentials, and configure the target project, a
default region such as us-central1 (check regional service availability at
Google Cloud locations), and a zone in that region. Cloud
mode takes the VM's zone from --zone, then compute/zone, then
us-central1-a; it does not read compute/region:
gcloud auth login
gcloud auth application-default login
gcloud config set project {project_id}
gcloud config set compute/region {region}
gcloud config set compute/zone {zone}
Output bucket: This skill writes the ZIP archives to a Cloud Storage
bucket (--output) that the project owns and that is not public, in both
run modes. In both modes the command checks the bucket before it starts
the extractor, so the caller needs storage.buckets.list on the bucket's
project and storage.buckets.getIamPolicy on the bucket unless public
access prevention is enforced on it. In local mode the Application Default
Credentials also need to create objects in the bucket.
Run-mode prerequisites (ask the user before installing software or
enabling APIs):
- Local mode (
--run-mode=local): Java 11 or later (JRE 11+) on
PATH, the bqms gcloud component, vendor JDBC driver JARs
(--driver) when required, and local network access to the database;
see the local extraction guide.
Without a terminal the extract command cannot prompt to install
bqms and fails, so once the user agrees, install it first with
gcloud components install bqms --quiet (for a gcloud installed with
apt or yum, install the google-cloud-cli-bqms package instead).
- Cloud mode (
--run-mode=cloud):
- APIs: Compute Engine (
compute.googleapis.com), Secret Manager
(secretmanager.googleapis.com), Cloud Storage
(storage.googleapis.com), and Cloud Logging
(logging.googleapis.com).
- Network and subnet: A VPC
--network and --subnet (default
default) with routing to the database, and the VM's egress IP
range allowlisted by the database. The VM also needs internet
access to download the extractor and reach Google APIs: keep the
default external IP, or with --no-address add Cloud NAT in the
VM's region. A Cloud VPN or Cloud Interconnect route to the
database does not replace it.
- IAM permissions: The caller needs roles to create the VM and
the secret, for example Compute Admin (
roles/compute.admin) and
Secret Manager Admin (roles/secretmanager.admin), plus the
bucket permissions in item 2. With a prepared
--gce-service-account, the caller also needs Service Account User
(roles/iam.serviceAccountUser) on that account, and the account
itself needs the roles listed in the
cloud extraction guide.
- Default service account: Without
--gce-service-account, every
run creates or reuses
bqms-cloud-extractor@{project_id}.iam.gserviceaccount.com and
grants it nine project roles, including roles/editor,
roles/compute.admin, and roles/cloudkms.cryptoKeyDecrypter.
This needs Service Account Admin (roles/iam.serviceAccountAdmin)
and Project IAM Admin (roles/resourcemanager.projectIamAdmin).
Without a terminal, gcloud accepts its own "create it?" prompt, so
get the user's explicit approval before you omit
--gce-service-account; see the
cloud extraction guide.
Cost and cleanup: Review
Google Cloud Pricing for Compute Engine,
Secret Manager, and Cloud Storage. After a cloud run, delete the run's
Secret Manager secret (Section 5).
1. Choose the Run Mode
| |
--run-mode=local (default) |
--run-mode=cloud (Cloud Extractor) |
| Where the extractor runs |
This machine (needs a Java runtime) |
Temporary Compute Engine VM |
| Network path to the DB |
This machine's network (VPN, on-prem, bastion) |
The VM's VPC; the DB must accept connections from it |
| JDBC drivers |
Any, including vendor JARs passed with --driver |
Only drivers bundled with the extractor; the VM has no vendor JARs and cannot read a local --driver path |
| Credentials |
@FILE response file read by the local extractor |
--password (read from a file) stored with the other extractor arguments in a Secret Manager secret that the VM reads |
| Failed extractor tasks |
Partial ZIP still lands in --output |
No ZIP is uploaded; the run fails |
| Read next |
local extraction guide |
cloud extraction guide |
Pick local when any of these is true:
- The connector needs a JDBC driver that the cloud VM does not have:
teradata, teradata-logs, teradata14-logs, oracle, oracle-logs,
oracle-stats, sqlserver, mysql, netezza, vertica, vertica-logs.
Locally, the bqms launcher downloads the Oracle, SQL Server, and MySQL
drivers itself (the local extractor ships without JDBC drivers, so the
launcher also downloads the Snowflake, Redshift, PostgreSQL, and Greenplum
drivers); Teradata, Netezza, and Vertica need --driver (Section 3).
- The database is only reachable from the user's network (on-prem, VPN,
private endpoint with no route from Google Cloud).
- The user cannot create Compute Engine VMs, service accounts, or secrets in
the project.
--host is localhost, 127.0.0.1, ::1, or a private address with no
route from a Google Cloud VPC. Never use --run-mode=cloud for these; the
VM cannot reach them (on the VM, 127.0.0.1 is the VM itself, even if the
user says the address is routed). Ask for a routable address, or, if the
user already accepted a fallback, run locally with the same --output and
drop the cloud-only flags.
Pick cloud when the database is reachable from Google Cloud (Snowflake,
Redshift, BigQuery, cloud-hosted PostgreSQL/Greenplum, Hive metastore) and the
user wants nothing installed locally.
Tell the user which run mode you chose and why, for example that cloud mode
cannot load vendor JDBC drivers or reach a database that only the user's network
can reach.
2. Connectors and Commands
Each connector extracts either catalog metadata or query logs, so it
belongs to exactly one command. Run both commands to get both.
| Source |
metadata extract connector |
query-logs extract connector |
| Snowflake |
snowflake (or snowflake-account-usage-metadata, snowflake-information-schema-metadata) |
snowflake-logs (or snowflake-account-usage-logs, snowflake-information-schema-logs) |
| Amazon Redshift |
redshift |
redshift-raw-logs (or redshift-logs, redshift-serverless-logs) |
| Teradata |
teradata |
teradata-logs (version 15+), teradata14-logs |
| Oracle |
oracle (DDL), oracle-stats (workload statistics for assessment; window set by --query-log-days) |
oracle-logs |
| Google BigQuery |
bigquery |
bigquery-logs |
| Greenplum |
greenplum |
greenplum-logs |
| Vertica |
vertica |
vertica-logs |
| PostgreSQL |
postgresql |
none |
| SQL Server / Azure |
sqlserver |
none |
| MySQL |
mysql |
none |
| Netezza |
netezza |
none |
| Apache Hive metastore |
hiveql |
none |
[!TIP]
Catalog metadata is fast (minutes) and is all that SQL translation,
schema creation, Parquet export, and static lineage need. Query logs take
longer and are only needed for workload assessment or lineage of queries that
are not stored as views (for example dbt or Airflow INSERT ... SELECT jobs).
Always bound log extraction with --query-log-start/--query-log-end or
--query-log-days (after --).
3. Flags
gcloud flags (before --):
| Flag |
Run mode |
Meaning |
--connector |
both |
Required. Must match the command (Section 2). |
--output |
both |
Required. gs://BUCKET/PATH/ (or a .zip object path). Local mode also accepts a local file or directory path. |
--run-mode |
both |
local (default) or cloud. |
--expected-owner |
both |
Project that must own the --output bucket (defaults to the current project). |
--endpoint-mode |
both |
Standard gcloud regional endpoint selection; leave unset unless the user asks. |
--project |
both |
Standard gcloud project flag. |
--zone, --secret, --network, and others |
cloud only |
See the cloud extraction guide. |
Extractor flags (after --):
| Flag |
Meaning |
--host, --port, --url |
Server address, or a full JDBC URL that overrides host and port. |
--user, @FILE |
User, and a response file holding the password (Section 4). |
--database, --schema |
Comma-separated lists to limit what is extracted. postgresql extracts only the first --database and ignores --schema; run it once per database, each with its own --output. |
--warehouse, --role |
Snowflake session settings (set per session; never change user defaults). |
--assessment |
Extra data for BigQuery Migration Assessment (omit --database and --schema when passing --assessment to snowflake, which rejects combining them). Only changes the output of teradata, teradata-logs, redshift, redshift-raw-logs, snowflake, snowflake-logs, hiveql, and greenplum-logs; other connectors ignore it. |
--query-log-start, --query-log-end, --query-log-days |
Query log window for *-logs connectors. |
--driver |
Vendor JDBC driver JAR(s) (required for teradata*, vertica*, and netezza; optional override when the local bqms launcher downloads the driver). Local mode only. |
4. Credentials
Never type a literal password on the command line, and never ask the user to
paste it into the chat. Ask the user to run the password-file command below in
their own terminal, with DB_PASSWORD set there (for example with
read -rs DB_PASSWORD && export DB_PASSWORD in bash or zsh), or to create
the file another way, and to give you only its path. The commands stop with an
error instead of writing an empty password when DB_PASSWORD is unset. Which
mechanism to use depends on the run mode.
Local mode: @FILE response file. The local extractor reads @FILE
arguments as YAML response files and turns each key into a flag, so a file with
password: ... supplies --password without putting it on the command line.
Write it so only the user can read it, with the value single-quoted (any '
doubled) so special characters survive. This form works in bash, zsh, and
sh and assumes a single-line password:
: "${DB_PASSWORD:?DB_PASSWORD is not set}" && umask 077 && printf "password: '%s'\n" "$(printf '%s' "$DB_PASSWORD" | sed "s/'/''/g")" > ./extractor-secret.yaml
Pass @./extractor-secret.yaml after -- (for a file outside the current
directory use an absolute path such as @$HOME/path/file.yaml; @~/... is not
expanded).
Cloud mode: --password read from a file. The CLI stores the extractor
arguments in Secret Manager exactly as given, and the VM runs the extractor with
them. The VM has none of your local files, so an @FILE argument fails there.
Put the password in a file only the user can read and pass it with command
substitution after --:
: "${DB_PASSWORD:?DB_PASSWORD is not set}" && umask 077 && printf '%s' "${DB_PASSWORD}" > ./db_password
-- ... --password="$(cat ./db_password)"
The value then lives in the run's Secret Manager secret (delete it after the
run, Section 5) and is visible in this machine's process list while gcloud runs,
so use a machine only the user can access.
Delete the password file when done if you created it; leave a file the user
supplied in place.
5. Quick Start
Local (Teradata on-prem with the vendor driver):
gcloud alpha bq migration metadata extract \
--connector=teradata \
--output="gs://{bucket}/teradata_metadata/" \
-- \
--host="{teradata_host}" \
--user="{db_user}" \
--driver="{path_to}/terajdbc4.jar" \
--assessment \
@./extractor-secret.yaml
Cloud (Snowflake through the Cloud Extractor):
gcloud alpha bq migration metadata extract \
--connector=snowflake \
--output="gs://{bucket}/snowflake_metadata/{run_label}/" \
--run-mode=cloud \
--project="{project_id}" \
--zone="{zone}" \
--secret="bqms-extractor-creds-{run_label}" \
-- \
--host="{account_identifier}.snowflakecomputing.com" \
--user="{db_user}" \
--database="{db_name}" \
--warehouse="{db_warehouse}" \
--role="{db_role}" \
--password="$(cat ./db_password)"
--secret is optional. Without it the CLI creates
bqms-extractor-creds-run-EPOCH_SECONDS, prints its name, and labels it
goog-bqms-extractor-run-id. If you pass --secret, use a new {run_label}
for every run: for a secret that already exists the CLI only adds a version,
and deleting that secret later would also delete its earlier versions.
Give every cloud run an --output prefix that holds no .zip yet, such as a
new {run_label} folder. gcloud reports success as soon as it finds any
non-empty .zip under --output, without checking when it was written. An
older archive there (from an earlier metadata, query-log, or local run) ends
the command at once with Extraction complete while the new VM is still
starting, and deleting the secret then breaks the real run. If the user names a
prefix, use it as given after checking that gcloud storage ls "gs://{bucket}/{path}/" lists no .zip; otherwise propose a new sub-prefix.
Every answer that runs or recommends an extraction must include these steps, in
order, with their commands:
- Check the exit code and task summary (Section 6, step 1).
- Verify the output with the statistics template (Section 6, step 2):
cp <SKILL_DIR>/assets/extraction_stats_template.py ./extraction_stats.py && python3 ./extraction_stats.py --input "gs://{bucket}/{path}/".
- After a cloud run, delete the run's Secret Manager secret once steps 1 and 2
are done (or right away if the run failed), and say why: it still holds the
database password. Delete the
--secret name if you passed one: gcloud secrets delete bqms-extractor-creds-{run_label} --project={project_id} --quiet. Otherwise delete the auto-named
bqms-extractor-creds-run-EPOCH_SECONDS secret the command printed, or find
it with gcloud secrets list --filter="labels.goog-bqms-extractor-run-id:*". Only delete secrets created
for this run; if the user had you reuse an existing secret, record its
newest version before the run and destroy only a version this run added.
See the cleanup section of the
cloud extraction guide.
- Delete
extractor-secret.yaml or db_password if you created it; leave a
file the user supplied in place.
6. Check the Result
Exit code and task summary. The extractor exits 1 when any required task
fails. What that means depends on the run mode:
- Local: gcloud reports
BigQuery Migration Extractor failed with exit code 1, but the extractor has already written the ZIP to --output,
with a *.exception.txt entry for each failure. A ZIP present after
this error is a partial success, not a reason to rerun.
- Cloud: the VM uploads the ZIP only when the extractor exits 0, so
Extraction complete means success only if --output held no .zip
before the run (Section 5). If any required task fails, nothing is
uploaded and gcloud reports Extraction finished, but no non-empty artifact was found. Always tell the user both next steps: read the
VM's logs to see why the extractor failed, and then either fix the
cause and rerun, or rerun with --run-mode=local, which keeps the
partial ZIP. gcloud prints a gcloud logging read 'resource.type="gce_instance" AND resource.labels.instance_id="..."'
command only when it finds error entries; otherwise query the logs by
the VM name from the Instance Link: line (see Monitoring in the
cloud extraction guide). Also delete
the run's secret (Section 5).
For a local partial ZIP, read the N TASKS SUCCEEDED / N TASKS FAILED
summary and the exception entries (unzip -l {archive}.zip '*.exception.txt' lists the failed tasks; each file is long, so read just
the error with unzip -p {archive}.zip '{task}.exception.txt' | grep -m 3 -E '^\s*message:'). A failure is harmless when a version-specific fallback of
the same task succeeded; the fallback has a version suffix, for example
functions.csv.exception.txt next to a populated functions-11.csv on
PostgreSQL 11 and later.
Archive integrity and statistics. Copy the
extraction statistics template
(<SKILL_DIR>/assets/extraction_stats_template.py, where <SKILL_DIR> is
the directory that contains this SKILL.md) into the working directory and
run it on the ZIP, its folder, or the GCS prefix:
cp <SKILL_DIR>/assets/extraction_stats_template.py ./extraction_stats.py
python3 ./extraction_stats.py --input "gs://{bucket}/snowflake_metadata/"
If the gs:// form fails, copy the ZIP locally with gcloud storage cp and
run the script on the local file.
It rejects incomplete archives (22-byte placeholders) and reports the
extractor telemetry status, counts of databases, schemas, base tables,
views, and columns, total base table row counts and byte sizes, per-schema
and per-table breakdowns, and the column data type distribution. See the
statistics report specification
for the full report metrics and the # CUSTOMIZE: extension points (for
example, editing _EXCLUDED_SYSTEM_SCHEMAS or _should_include_schema() to
drop a STAGING_TMP schema).
[!NOTE]
The script computes object, row, and byte counts from the Snowflake, Redshift
(svv_*), and Teradata (dbc.*V) catalog layouts, and table, view, and
top-level column counts from the BigQuery tables.jsonl (rows and sizes show
as N/A there because that connector does not record them). It reads the run
status from dumper-telemetry.jsonl when the archive has one. For other
layouts (for example PostgreSQL, Oracle, or the Hive tables.jsonl) it still
validates the archive and reports the connector and host, but object counts
are zero and the run status can show UNKNOWN. On PostgreSQL 11 and later it
shows FAILURE because of the harmless functions.csv and aggregates.csv
fallbacks (Section 6, step 1). Then use the task summary printed by the
extract command for the run status, and count objects from the connector's
own files (Section 7).
7. Output Archives
Unless --output names a .zip object, the extractor names the ZIP
dwh-migration-{connector}-metadata.zip for metadata extract connectors and
dwh-migration-{connector}.zip for query-logs extract connectors, whose names
already end in -logs: snowflake writes
dwh-migration-snowflake-metadata.zip and snowflake-logs writes
dwh-migration-snowflake-logs.zip. A few names differ (oracle-stats writes
dwh-migration-oracle-stats.zip; hiveql, and query logs extracted with
--assessment, add a -YYYYMMDDTHHMMSS timestamp), so list --output to find
the archive. Every archive contains compilerworks-metadata.yaml (extractor
version and arguments) plus connector-specific CSVs:
dwh-migration-snowflake-metadata.zip
├── databases-au.csv # Databases and owners
├── schemata-au.csv # Schemas
├── tables-au.csv # Tables, row counts, byte sizes
├── columns-au.csv # Columns, ordinal positions, types, nullability
├── views-au.csv # View DDL
├── functions-au.csv # User-defined and external functions
├── external_tables.csv # External stage tables
├── snowflake.yaml # Assessment flag and warehouse count
├── compilerworks-metadata.yaml # Extractor version and flags
└── dumper-telemetry.jsonl # Task timings
dwh-migration-snowflake-logs.zip
├── query_history_2026-08-05T000000Z.csv # One file per rotation window
├── ...
└── compilerworks-metadata.yaml
dwh-migration-postgresql-metadata.zip
├── database.csv # Databases on the server
├── is_columns_private.csv # Columns of user tables and views
├── pg_views_private.csv # User view DDL
├── functions-11.csv # Functions on search_path (11+; drop pg_catalog)
├── aggregates-11.csv, types.csv, casts.csv
├── is_columns_generic.csv, pg_views_generic.csv # System catalog objects
├── pg_user.csv # Roles and privileges (passwords masked)
├── *.csv.exception.txt # One per failed task
└── compilerworks-{metadata.yaml,arguments.txt,version.txt,format.txt}
In PostgreSQL archives the *_private.csv files hold user objects and the
*_generic.csv files hold system objects. is_columns_private.csv includes
view columns; subtract the views in pg_views_private.csv to count base tables.
functions-11.csv, aggregates-11.csv, and types.csv list only objects
visible on the session search_path ("$user", public by default), so other
schemas' functions and types are missing. To include them, replace --host,
--port, and --database with
--url="jdbc:postgresql://{host}:{port}/{db_name}?currentSchema={schema1},{schema2},public".
Archives never contain the database password: to confirm, check that
compilerworks-arguments.txt and compilerworks-metadata.yaml have no
password entry (scanning for the password value is unreliable when it is a
common word), but they do contain object DDL and role lists, and
dumper-telemetry.jsonl is copied from
~/.cache/DWH-Dumper/all-dumper-telemetry.jsonl, so it can hold connectors,
hosts, and errors from earlier extractor runs on the same machine. Treat them
as sensitive and keep them in a private bucket.
8. Downstream BigQuery Workflows
Each cloud run writes to its own --output prefix (Section 5). To hand the
archives over together, copy the finished ZIPs into one shared prefix, for
example gcloud storage cp "gs://{bucket}/{run_prefix}/*.zip" "gs://{bucket}/assessment/", and never use that shared prefix as a cloud
--output. Then:
- BigQuery Migration Assessment: direct the user to BigQuery >
Migration > Assessment in the Google Cloud Console and give it the GCS
prefix that holds the metadata (extracted with
--assessment) and logs.
- Batch SQL translation: attach the metadata ZIP as schema context in
BigQuery > Migration > SQL translation, or through the BigQuery
Migration API / BQMS MCP tools (see
bigquery-migration-sql-translator).
- Table data transfer (out of scope): this skill extracts catalog metadata
and query logs only. If the user asks to export or load table rows (such as
Snowflake
COPY INTO unloads), ask whether they want to use the
BigQuery Data Transfer Service (including the
Snowflake transfer connector) or export to Parquet/CSV in
Cloud Storage and load with bq load.
1---2name: bigquery-migration-metadata-extractor3metadata:4 source: https://skillmd.com/skills/google/bigquery-migration-metadata-extractor5 version: "1.0.0"6 category: BigDataAndAnalytics7 publisher: google8description: >-9 Extracts database catalog metadata and query logs from data warehouses by10 running the DWH migration extractor locally or on a temporary Google Cloud VM11 through the BigQuery Migration Cloud Extractor (credentials held in Secret12 Manager), and inspects extracted `.zip` archives to report object and volume13 statistics. Use when extracting database metadata or query logs from14 Snowflake, Redshift, Teradata, Oracle, PostgreSQL, SQL Server, Hive, etc.,15 deciding between local and cloud (Cloud Extractor) extraction, or inspecting16 extracted dwh-migration-*.zip archives to print post-extraction statistics.17 Don't use for SQL dialect translation (use bigquery-migration-sql-translator),18 exporting table data to Parquet or CSV files (such as Snowflake `COPY INTO`19 unloads) or loading it into BigQuery (use the BigQuery Data Transfer Service20 or bigquery-basics), or validating migrated data.21---2223# BigQuery Migration Metadata Extraction2425Extraction runs the DWH migration extractor (`dwh-migration-dumper`) through two26gcloud commands to collect catalog DDL and query history—not table row data. If27the user wants to migrate table rows (such as Snowflake `COPY INTO` unloads),28ask whether they prefer the [BigQuery Data Transfer Service][bq_dts] (including29the [Snowflake connector][snowflake_dts]) or exporting to Cloud Storage and30loading with `bq load`. Both extract commands are in the **alpha** release31track:3233Command | Use for34:--------------------------------------------- | :------35`gcloud alpha bq migration metadata extract` | Catalog metadata (metadata connectors)36`gcloud alpha bq migration query-logs extract` | Query history (`*-logs` connectors)3738Both take the same arguments. gcloud flags come first; the extractor's own flags39come after `--` and are passed to the extractor unchanged:4041```text42gcloud alpha bq migration {metadata|query-logs} extract \43 --connector=CONNECTOR --output=gs://BUCKET/PATH/ \44 [--run-mode=local|cloud] [cloud flags] \45 -- [EXTRACTOR FLAGS] [PASSWORD]46```4748* `--connector` and `--output` are required gcloud flags. `--output` is a49 Cloud Storage path (`gs://...`); local mode also accepts a local file or50 directory path. This skill uses Cloud Storage in both run modes.51* `--run-mode=local` (the default) runs the extractor on this machine.52 `--run-mode=cloud` runs it on a temporary Compute Engine VM (the **BigQuery53 Migration Cloud Extractor**).54* Database flags (`--host`, `--port`, `--user`, `--database`, `--schema`,55 `--warehouse`, `--role`, `--driver`, `--assessment`, `--query-log-*`, ...)56 go after `--`. Putting them before `--` is an error.57* How the password is passed depends on the run mode (Section 4): an extractor58 response file (`@FILE`) in local mode, and `--password` read from a file in59 cloud mode. Never type a literal password.6061> [!IMPORTANT]62> The commands are in the alpha track. If gcloud reports an invalid choice or63> unknown command, ask the user before updating the Google Cloud CLI, then64> retry. Update it with `gcloud components update`, or, if gcloud was installed65> with a system package manager such as `apt` or `yum` (which disables the66> component manager), upgrade the `google-cloud-cli` package with that package67> manager.6869## Usage Attribution7071**MUST READ**: Before executing any `gcloud`, REST API, or MCP command, you72MUST read and follow the instructions in73[Usage Attribution](references/attribution.md).7475## Before you begin76771. **Google Cloud CLI, authentication, project, region, and zone**: Ensure the78 [Google Cloud CLI][gcloud_install] is installed, verify that the Google79 Cloud project has an active Cloud Billing account, authenticate with user80 and Application Default Credentials, and configure the target project, a81 default region such as `us-central1` (check regional service availability at82 [Google Cloud locations][cloud_locations]), and a zone in that region. Cloud83 mode takes the VM's zone from `--zone`, then `compute/zone`, then84 `us-central1-a`; it does not read `compute/region`:8586 ```bash87 gcloud auth login88 gcloud auth application-default login89 gcloud config set project {project_id}90 gcloud config set compute/region {region}91 gcloud config set compute/zone {zone}92 ```93942. **Output bucket**: This skill writes the ZIP archives to a Cloud Storage95 bucket (`--output`) that the project owns and that is not public, in both96 run modes. In both modes the command checks the bucket before it starts97 the extractor, so the caller needs `storage.buckets.list` on the bucket's98 project and `storage.buckets.getIamPolicy` on the bucket unless public99 access prevention is enforced on it. In local mode the Application Default100 Credentials also need to create objects in the bucket.1011023. **Run-mode prerequisites** (ask the user before installing software or103 enabling APIs):104105 * **Local mode (`--run-mode=local`)**: Java 11 or later (JRE 11+) on106 `PATH`, the `bqms` gcloud component, vendor JDBC driver JARs107 (`--driver`) when required, and local network access to the database;108 see the [local extraction guide](references/local_extraction.md).109 Without a terminal the extract command cannot prompt to install110 `bqms` and fails, so once the user agrees, install it first with111 `gcloud components install bqms --quiet` (for a gcloud installed with112 `apt` or `yum`, install the `google-cloud-cli-bqms` package instead).113 * **Cloud mode (`--run-mode=cloud`)**:114 * **APIs**: Compute Engine (`compute.googleapis.com`), Secret Manager115 (`secretmanager.googleapis.com`), Cloud Storage116 (`storage.googleapis.com`), and Cloud Logging117 (`logging.googleapis.com`).118 * **Network and subnet**: A VPC `--network` and `--subnet` (default119 `default`) with routing to the database, and the VM's egress IP120 range allowlisted by the database. The VM also needs internet121 access to download the extractor and reach Google APIs: keep the122 default external IP, or with `--no-address` add Cloud NAT in the123 VM's region. A Cloud VPN or Cloud Interconnect route to the124 database does not replace it.125 * **IAM permissions**: The caller needs roles to create the VM and126 the secret, for example Compute Admin (`roles/compute.admin`) and127 Secret Manager Admin (`roles/secretmanager.admin`), plus the128 bucket permissions in item 2. With a prepared129 `--gce-service-account`, the caller also needs Service Account User130 (`roles/iam.serviceAccountUser`) on that account, and the account131 itself needs the roles listed in the132 [cloud extraction guide](references/cloud_extraction.md).133 * **Default service account**: Without `--gce-service-account`, every134 run creates or reuses135 `bqms-cloud-extractor@{project_id}.iam.gserviceaccount.com` and136 grants it nine project roles, including `roles/editor`,137 `roles/compute.admin`, and `roles/cloudkms.cryptoKeyDecrypter`.138 This needs Service Account Admin (`roles/iam.serviceAccountAdmin`)139 and Project IAM Admin (`roles/resourcemanager.projectIamAdmin`).140 Without a terminal, gcloud accepts its own "create it?" prompt, so141 get the user's explicit approval before you omit142 `--gce-service-account`; see the143 [cloud extraction guide](references/cloud_extraction.md).1441454. **Cost and cleanup**: Review146 [Google Cloud Pricing](https://cloud.google.com/pricing) for Compute Engine,147 Secret Manager, and Cloud Storage. After a cloud run, delete the run's148 Secret Manager secret (Section 5).149150## 1. Choose the Run Mode151152 | `--run-mode=local` (default) | `--run-mode=cloud` (Cloud Extractor)153:----------------------- | :------------------------------------------------------- | :-----------------------------------154Where the extractor runs | This machine (needs a Java runtime) | Temporary Compute Engine VM155Network path to the DB | This machine's network (VPN, on-prem, bastion) | The VM's VPC; the DB must accept connections from it156JDBC drivers | Any, including vendor JARs passed with `--driver` | Only drivers bundled with the extractor; the VM has no vendor JARs and cannot read a local `--driver` path157Credentials | `@FILE` response file read by the local extractor | `--password` (read from a file) stored with the other extractor arguments in a Secret Manager secret that the VM reads158Failed extractor tasks | Partial ZIP still lands in `--output` | No ZIP is uploaded; the run fails159Read next | [local extraction guide](references/local_extraction.md) | [cloud extraction guide](references/cloud_extraction.md)160161Pick **local** when any of these is true:162163* The connector needs a JDBC driver that the cloud VM does not have:164 `teradata`, `teradata-logs`, `teradata14-logs`, `oracle`, `oracle-logs`,165 `oracle-stats`, `sqlserver`, `mysql`, `netezza`, `vertica`, `vertica-logs`.166 Locally, the `bqms` launcher downloads the Oracle, SQL Server, and MySQL167 drivers itself (the local extractor ships without JDBC drivers, so the168 launcher also downloads the Snowflake, Redshift, PostgreSQL, and Greenplum169 drivers); Teradata, Netezza, and Vertica need `--driver` (Section 3).170* The database is only reachable from the user's network (on-prem, VPN,171 private endpoint with no route from Google Cloud).172* The user cannot create Compute Engine VMs, service accounts, or secrets in173 the project.174* `--host` is `localhost`, `127.0.0.1`, `::1`, or a private address with no175 route from a Google Cloud VPC. Never use `--run-mode=cloud` for these; the176 VM cannot reach them (on the VM, `127.0.0.1` is the VM itself, even if the177 user says the address is routed). Ask for a routable address, or, if the178 user already accepted a fallback, run locally with the same `--output` and179 drop the cloud-only flags.180181Pick **cloud** when the database is reachable from Google Cloud (Snowflake,182Redshift, BigQuery, cloud-hosted PostgreSQL/Greenplum, Hive metastore) and the183user wants nothing installed locally.184185Tell the user which run mode you chose and why, for example that cloud mode186cannot load vendor JDBC drivers or reach a database that only the user's network187can reach.188189## 2. Connectors and Commands190191Each connector extracts **either** catalog metadata **or** query logs, so it192belongs to exactly one command. Run both commands to get both.193194Source | `metadata extract` connector | `query-logs extract` connector195:-------------------- | :---------------------------------------------------------------------------------------------------- | :-----------------------------196Snowflake | `snowflake` (or `snowflake-account-usage-metadata`, `snowflake-information-schema-metadata`) | `snowflake-logs` (or `snowflake-account-usage-logs`, `snowflake-information-schema-logs`)197Amazon Redshift | `redshift` | `redshift-raw-logs` (or `redshift-logs`, `redshift-serverless-logs`)198Teradata | `teradata` | `teradata-logs` (version 15+), `teradata14-logs`199Oracle | `oracle` (DDL), `oracle-stats` (workload statistics for assessment; window set by `--query-log-days`) | `oracle-logs`200Google BigQuery | `bigquery` | `bigquery-logs`201Greenplum | `greenplum` | `greenplum-logs`202Vertica | `vertica` | `vertica-logs`203PostgreSQL | `postgresql` | none204SQL Server / Azure | `sqlserver` | none205MySQL | `mysql` | none206Netezza | `netezza` | none207Apache Hive metastore | `hiveql` | none208209> [!TIP]210> Catalog metadata is fast (minutes) and is all that SQL translation,211> schema creation, Parquet export, and static lineage need. Query logs take212> longer and are only needed for workload assessment or lineage of queries that213> are not stored as views (for example dbt or Airflow `INSERT ... SELECT` jobs).214> Always bound log extraction with `--query-log-start`/`--query-log-end` or215> `--query-log-days` (after `--`).216217## 3. Flags218219gcloud flags (before `--`):220221Flag | Run mode | Meaning222:-------------------------------------------- | :--------- | :------223`--connector` | both | Required. Must match the command (Section 2).224`--output` | both | Required. `gs://BUCKET/PATH/` (or a `.zip` object path). Local mode also accepts a local file or directory path.225`--run-mode` | both | `local` (default) or `cloud`.226`--expected-owner` | both | Project that must own the `--output` bucket (defaults to the current project).227`--endpoint-mode` | both | Standard gcloud regional endpoint selection; leave unset unless the user asks.228`--project` | both | Standard gcloud project flag.229`--zone`, `--secret`, `--network`, and others | cloud only | See the [cloud extraction guide](references/cloud_extraction.md).230231Extractor flags (after `--`):232233Flag | Meaning234:--------------------------------------------------------- | :------235`--host`, `--port`, `--url` | Server address, or a full JDBC URL that overrides host and port.236`--user`, `@FILE` | User, and a response file holding the password (Section 4).237`--database`, `--schema` | Comma-separated lists to limit what is extracted. `postgresql` extracts only the first `--database` and ignores `--schema`; run it once per database, each with its own `--output`.238`--warehouse`, `--role` | Snowflake session settings (set per session; never change user defaults).239`--assessment` | Extra data for BigQuery Migration Assessment (omit `--database` and `--schema` when passing `--assessment` to `snowflake`, which rejects combining them). Only changes the output of `teradata`, `teradata-logs`, `redshift`, `redshift-raw-logs`, `snowflake`, `snowflake-logs`, `hiveql`, and `greenplum-logs`; other connectors ignore it.240`--query-log-start`, `--query-log-end`, `--query-log-days` | Query log window for `*-logs` connectors.241`--driver` | Vendor JDBC driver JAR(s) (required for `teradata*`, `vertica*`, and `netezza`; optional override when the local `bqms` launcher downloads the driver). Local mode only.242243## 4. Credentials244245Never type a literal password on the command line, and never ask the user to246paste it into the chat. Ask the user to run the password-file command below in247their own terminal, with `DB_PASSWORD` set there (for example with248`read -rs DB_PASSWORD && export DB_PASSWORD` in `bash` or `zsh`), or to create249the file another way, and to give you only its path. The commands stop with an250error instead of writing an empty password when `DB_PASSWORD` is unset. Which251mechanism to use depends on the run mode.252253**Local mode: `@FILE` response file.** The local extractor reads `@FILE`254arguments as YAML response files and turns each key into a flag, so a file with255`password: ...` supplies `--password` without putting it on the command line.256Write it so only the user can read it, with the value single-quoted (any `'`257doubled) so special characters survive. This form works in `bash`, `zsh`, and258`sh` and assumes a single-line password:259260```bash261: "${DB_PASSWORD:?DB_PASSWORD is not set}" && umask 077 && printf "password: '%s'\n" "$(printf '%s' "$DB_PASSWORD" | sed "s/'/''/g")" > ./extractor-secret.yaml262```263264Pass `@./extractor-secret.yaml` after `--` (for a file outside the current265directory use an absolute path such as `@$HOME/path/file.yaml`; `@~/...` is not266expanded).267268**Cloud mode: `--password` read from a file.** The CLI stores the extractor269arguments in Secret Manager exactly as given, and the VM runs the extractor with270them. The VM has none of your local files, so an `@FILE` argument fails there.271Put the password in a file only the user can read and pass it with command272substitution after `--`:273274```bash275: "${DB_PASSWORD:?DB_PASSWORD is not set}" && umask 077 && printf '%s' "${DB_PASSWORD}" > ./db_password276```277278```text279-- ... --password="$(cat ./db_password)"280```281282The value then lives in the run's Secret Manager secret (delete it after the283run, Section 5) and is visible in this machine's process list while gcloud runs,284so use a machine only the user can access.285286Delete the password file when done if you created it; leave a file the user287supplied in place.288289## 5. Quick Start290291Local (Teradata on-prem with the vendor driver):292293```bash294gcloud alpha bq migration metadata extract \295 --connector=teradata \296 --output="gs://{bucket}/teradata_metadata/" \297 -- \298 --host="{teradata_host}" \299 --user="{db_user}" \300 --driver="{path_to}/terajdbc4.jar" \301 --assessment \302 @./extractor-secret.yaml303```304305Cloud (Snowflake through the Cloud Extractor):306307```bash308gcloud alpha bq migration metadata extract \309 --connector=snowflake \310 --output="gs://{bucket}/snowflake_metadata/{run_label}/" \311 --run-mode=cloud \312 --project="{project_id}" \313 --zone="{zone}" \314 --secret="bqms-extractor-creds-{run_label}" \315 -- \316 --host="{account_identifier}.snowflakecomputing.com" \317 --user="{db_user}" \318 --database="{db_name}" \319 --warehouse="{db_warehouse}" \320 --role="{db_role}" \321 --password="$(cat ./db_password)"322```323324`--secret` is optional. Without it the CLI creates325`bqms-extractor-creds-run-EPOCH_SECONDS`, prints its name, and labels it326`goog-bqms-extractor-run-id`. If you pass `--secret`, use a new `{run_label}`327for every run: for a secret that already exists the CLI only adds a version,328and deleting that secret later would also delete its earlier versions.329330Give every cloud run an `--output` prefix that holds no `.zip` yet, such as a331new `{run_label}` folder. gcloud reports success as soon as it finds any332non-empty `.zip` under `--output`, without checking when it was written. An333older archive there (from an earlier metadata, query-log, or local run) ends334the command at once with `Extraction complete` while the new VM is still335starting, and deleting the secret then breaks the real run. If the user names a336prefix, use it as given after checking that `gcloud storage ls337"gs://{bucket}/{path}/"` lists no `.zip`; otherwise propose a new sub-prefix.338339Every answer that runs or recommends an extraction must include these steps, in340order, with their commands:3413421. Check the exit code and task summary (Section 6, step 1).3432. Verify the output with the statistics template (Section 6, step 2): `cp344 <SKILL_DIR>/assets/extraction_stats_template.py ./extraction_stats.py &&345 python3 ./extraction_stats.py --input "gs://{bucket}/{path}/"`.3463. After a cloud run, delete the run's Secret Manager secret once steps 1 and 2347 are done (or right away if the run failed), and say why: it still holds the348 database password. Delete the `--secret` name if you passed one: `gcloud349 secrets delete bqms-extractor-creds-{run_label} --project={project_id}350 --quiet`. Otherwise delete the auto-named351 `bqms-extractor-creds-run-EPOCH_SECONDS` secret the command printed, or find352 it with `gcloud secrets list353 --filter="labels.goog-bqms-extractor-run-id:*"`. Only delete secrets created354 for this run; if the user had you reuse an existing secret, record its355 newest version before the run and destroy only a version this run added.356 See the cleanup section of the357 [cloud extraction guide](references/cloud_extraction.md).3584. Delete `extractor-secret.yaml` or `db_password` if you created it; leave a359 file the user supplied in place.360361## 6. Check the Result3623631. **Exit code and task summary.** The extractor exits 1 when any required task364 fails. What that means depends on the run mode:365366 * **Local:** gcloud reports `BigQuery Migration Extractor failed with exit367 code 1`, but the extractor has already written the ZIP to `--output`,368 with a `*.exception.txt` entry for each failure. A ZIP present after369 this error is a partial success, not a reason to rerun.370 * **Cloud:** the VM uploads the ZIP only when the extractor exits 0, so371 `Extraction complete` means success only if `--output` held no `.zip`372 before the run (Section 5). If any required task fails, nothing is373 uploaded and gcloud reports `Extraction finished, but no non-empty374 artifact was found`. Always tell the user both next steps: read the375 VM's logs to see why the extractor failed, and then either fix the376 cause and rerun, or rerun with `--run-mode=local`, which keeps the377 partial ZIP. gcloud prints a `gcloud logging read378 'resource.type="gce_instance" AND resource.labels.instance_id="..."'`379 command only when it finds error entries; otherwise query the logs by380 the VM name from the `Instance Link:` line (see Monitoring in the381 [cloud extraction guide](references/cloud_extraction.md)). Also delete382 the run's secret (Section 5).383384 For a local partial ZIP, read the `N TASKS SUCCEEDED / N TASKS FAILED`385 summary and the exception entries (`unzip -l {archive}.zip386 '*.exception.txt'` lists the failed tasks; each file is long, so read just387 the error with `unzip -p {archive}.zip '{task}.exception.txt' | grep -m 3 -E388 '^\s*message:'`). A failure is harmless when a version-specific fallback of389 the same task succeeded; the fallback has a version suffix, for example390 `functions.csv.exception.txt` next to a populated `functions-11.csv` on391 PostgreSQL 11 and later.3922. **Archive integrity and statistics.** Copy the393 [extraction statistics template][extraction_stats_template]394 (`<SKILL_DIR>/assets/extraction_stats_template.py`, where `<SKILL_DIR>` is395 the directory that contains this `SKILL.md`) into the working directory and396 run it on the ZIP, its folder, or the GCS prefix:397398 ```bash399 cp <SKILL_DIR>/assets/extraction_stats_template.py ./extraction_stats.py400 python3 ./extraction_stats.py --input "gs://{bucket}/snowflake_metadata/"401 ```402403 If the `gs://` form fails, copy the ZIP locally with `gcloud storage cp` and404 run the script on the local file.405406 It rejects incomplete archives (22-byte placeholders) and reports the407 extractor telemetry status, counts of databases, schemas, base tables,408 views, and columns, total base table row counts and byte sizes, per-schema409 and per-table breakdowns, and the column data type distribution. See the410 [statistics report specification](references/extraction_stats_report.md)411 for the full report metrics and the `# CUSTOMIZE:` extension points (for412 example, editing `_EXCLUDED_SYSTEM_SCHEMAS` or `_should_include_schema()` to413 drop a `STAGING_TMP` schema).414415> [!NOTE]416> The script computes object, row, and byte counts from the Snowflake, Redshift417> (`svv_*`), and Teradata (`dbc.*V`) catalog layouts, and table, view, and418> top-level column counts from the BigQuery `tables.jsonl` (rows and sizes show419> as `N/A` there because that connector does not record them). It reads the run420> status from `dumper-telemetry.jsonl` when the archive has one. For other421> layouts (for example PostgreSQL, Oracle, or the Hive `tables.jsonl`) it still422> validates the archive and reports the connector and host, but object counts423> are zero and the run status can show `UNKNOWN`. On PostgreSQL 11 and later it424> shows `FAILURE` because of the harmless `functions.csv` and `aggregates.csv`425> fallbacks (Section 6, step 1). Then use the task summary printed by the426> extract command for the run status, and count objects from the connector's427> own files (Section 7).428429## 7. Output Archives430431Unless `--output` names a `.zip` object, the extractor names the ZIP432`dwh-migration-{connector}-metadata.zip` for `metadata extract` connectors and433`dwh-migration-{connector}.zip` for `query-logs extract` connectors, whose names434already end in `-logs`: `snowflake` writes435`dwh-migration-snowflake-metadata.zip` and `snowflake-logs` writes436`dwh-migration-snowflake-logs.zip`. A few names differ (`oracle-stats` writes437`dwh-migration-oracle-stats.zip`; `hiveql`, and query logs extracted with438`--assessment`, add a `-YYYYMMDDTHHMMSS` timestamp), so list `--output` to find439the archive. Every archive contains `compilerworks-metadata.yaml` (extractor440version and arguments) plus connector-specific CSVs:441442```text443dwh-migration-snowflake-metadata.zip444├── databases-au.csv # Databases and owners445├── schemata-au.csv # Schemas446├── tables-au.csv # Tables, row counts, byte sizes447├── columns-au.csv # Columns, ordinal positions, types, nullability448├── views-au.csv # View DDL449├── functions-au.csv # User-defined and external functions450├── external_tables.csv # External stage tables451├── snowflake.yaml # Assessment flag and warehouse count452├── compilerworks-metadata.yaml # Extractor version and flags453└── dumper-telemetry.jsonl # Task timings454455dwh-migration-snowflake-logs.zip456├── query_history_2026-08-05T000000Z.csv # One file per rotation window457├── ...458└── compilerworks-metadata.yaml459460dwh-migration-postgresql-metadata.zip461├── database.csv # Databases on the server462├── is_columns_private.csv # Columns of user tables and views463├── pg_views_private.csv # User view DDL464├── functions-11.csv # Functions on search_path (11+; drop pg_catalog)465├── aggregates-11.csv, types.csv, casts.csv466├── is_columns_generic.csv, pg_views_generic.csv # System catalog objects467├── pg_user.csv # Roles and privileges (passwords masked)468├── *.csv.exception.txt # One per failed task469└── compilerworks-{metadata.yaml,arguments.txt,version.txt,format.txt}470```471472In PostgreSQL archives the `*_private.csv` files hold user objects and the473`*_generic.csv` files hold system objects. `is_columns_private.csv` includes474view columns; subtract the views in `pg_views_private.csv` to count base tables.475`functions-11.csv`, `aggregates-11.csv`, and `types.csv` list only objects476visible on the session `search_path` (`"$user", public` by default), so other477schemas' functions and types are missing. To include them, replace `--host`,478`--port`, and `--database` with479`--url="jdbc:postgresql://{host}:{port}/{db_name}?currentSchema={schema1},{schema2},public"`.480481Archives never contain the database password: to confirm, check that482`compilerworks-arguments.txt` and `compilerworks-metadata.yaml` have no483`password` entry (scanning for the password value is unreliable when it is a484common word), but they do contain object DDL and role lists, and485`dumper-telemetry.jsonl` is copied from486`~/.cache/DWH-Dumper/all-dumper-telemetry.jsonl`, so it can hold connectors,487hosts, and errors from earlier extractor runs on the same machine. Treat them488as sensitive and keep them in a private bucket.489490## 8. Downstream BigQuery Workflows491492Each cloud run writes to its own `--output` prefix (Section 5). To hand the493archives over together, copy the finished ZIPs into one shared prefix, for494example `gcloud storage cp "gs://{bucket}/{run_prefix}/*.zip"495"gs://{bucket}/assessment/"`, and never use that shared prefix as a cloud496`--output`. Then:4974981. **BigQuery Migration Assessment**: direct the user to **BigQuery >499 Migration > Assessment** in the Google Cloud Console and give it the GCS500 prefix that holds the metadata (extracted with `--assessment`) and logs.5012. **Batch SQL translation**: attach the metadata ZIP as schema context in502 **BigQuery > Migration > SQL translation**, or through the BigQuery503 Migration API / BQMS MCP tools (see `bigquery-migration-sql-translator`).5043. **Table data transfer (out of scope)**: this skill extracts catalog metadata505 and query logs only. If the user asks to export or load table rows (such as506 Snowflake `COPY INTO` unloads), ask whether they want to use the507 [BigQuery Data Transfer Service][bq_dts] (including the508 [Snowflake transfer connector][snowflake_dts]) or export to Parquet/CSV in509 Cloud Storage and load with `bq load`.510511[bq_dts]: https://docs.cloud.google.com/bigquery/docs/dts-introduction.md.txt512[cloud_locations]: https://cloud.google.com/about/locations513[extraction_stats_template]: assets/extraction_stats_template.py514[gcloud_install]: https://cloud.google.com/sdk/docs/install515[snowflake_dts]: https://docs.cloud.google.com/bigquery/docs/migration/snowflake-transfer.md.txt