taufiqibrahim

export PROJECT_ID=<your_gcp_project_id_here>
export BQ_DATASET=<your_bigquery_dataset_name_here>

taufiqibrahim

# Delete subscription
gcloud pubsub subscriptions delete mysql.inventory.products-bq-sub

# Delete topic
gcloud pubsub topics delete mysql.inventory.products

# Recreate BigQuery table (Using SQL UI)
CREATE OR REPLACE TABLE
<project_id>.<dataset>.mysql_inventory_products_no_schema (
  data STRING

taufiqibrahim

# Create Pub/Sub schema
gcloud pubsub schemas create mysql.inventory.addresses-schema \
--type=AVRO \
--definition='{"type":"record","name":"MysqlInventoryAddressesSchema","fields":[{"type":"int","optional":false,"name":"id"},{"type":"int","optional":false,"name":"customer_id"},{"type":"string","optional":false,"name":"street"},{"type":"string","optional":false,"name":"city"},{"type":"string","optional":false,"name":"state"},{"type":"string","optional":false,"name":"zip"},{"type":"string","optional":false,"name":"type"},{"type":"string","optional":true,"name":"__op"},{"type":"string","optional":true,"name":"__table"},{"type":"long","optional":true,"name":"__source_ts_ms"},{"type":"string","optional":true,"name":"__deleted"}]}'

# Describe schema
gcloud pubsub schemas describe mysql.inventory.addresses-schema

# Create Pub/Sub topic with schema
gcloud pubsub topics create mysql.inventory.addresses --message-encoding=json --schema=mysql.inventory.addresses-schema

taufiqibrahim

SELECT t.table_catalog, t.table_schema, t.table_name,
	CASE WHEN primary_key_columns IS NOT NULL THEN 'true' ELSE 'false' END AS has_primary_key, primary_key_columns,
	CASE WHEN unique_key_columns IS NOT NULL THEN 'true' ELSE 'false' END AS has_unique_key, unique_key_columns,
	t.TABLE_ROWS as estimate_rows,
	ROUND(((data_length + index_length) / 1024 / 1024), 2) as table_size_mb
FROM information_schema.TABLES t
LEFT JOIN (
	SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') AS primary_key_columns
	FROM information_schema.`COLUMNS` c WHERE COLUMN_KEY IN ('PRI')
	GROUP BY TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_KEY

taufiqibrahim

{
  "version": 8,
  "name": "Dark Matter without labels",
  "metadata": {"maputnik:renderer": "mbgljs"},
  "sources": {
    "carto": {
      "type": "vector",
      "url": "https://tiles.basemaps.cartocdn.com/vector/carto.streets/v1/tiles.json"
    }
  },