Jdbc Schema Discovery

# Create crawler
aws glue create-crawler \
  --name "temp-discovery-crawler" \
  --role "<glue-service-role-arn>" \
  --database-name "temp_discovery_db" \
  --targets '{
    "JdbcTargets": [{
      "ConnectionName": "<connection-name>",
      "Path": "<database>/%"
    }]
  }' \
  --region <region>

# Start crawler
aws glue start-crawler --name "temp-discovery-crawler" --region <region>

# Check status (wait until State is READY)
aws glue get-crawler --name "temp-discovery-crawler" --region <region> \
  --query 'Crawler.State' --output text

aws glue get-tables --database-name "temp_discovery_db" --region <region>

I found these tables in your database:
1. CUSTOMERS (45 columns, ~1.2M rows)
2. ORDERS (32 columns, ~5.8M rows)
3. PRODUCTS (18 columns, ~15K rows)
4. INVENTORY (12 columns, ~250K rows)

Which one(s) should I import?

# Delete crawler
aws glue delete-crawler --name "temp-discovery-crawler" --region <region>

# Delete temp database (optional - tables still useful for reference)
aws glue delete-database --name "temp_discovery_db" --region <region>

aws glue get-table \
  --database-name "temp_discovery_db" \
  --name "<table-name>" \
  --region <region>

# test-schema.py
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Read just the schema (LIMIT 0)
df = spark.read.format("jdbc").options(
    url="<jdbc-url>",
    dbtable="(SELECT * FROM <table> WHERE 1=0) AS schema_query",
    user="<username>",
    password="<password>"
).load()

# Print schema
df.printSchema()

-- Query external database via Athena connector
SELECT * FROM "<athena-connector>"."<schema>"."<table>" LIMIT 0

SELECT *
FROM CUSTOMERS
WHERE created_date >= CURRENT_DATE - 90

SELECT *
FROM ORDERS
WHERE status IN ('COMPLETED', 'SHIPPED')

SELECT
  o.order_id,
  o.order_date,
  c.customer_name,
  c.email,
  SUM(oi.quantity * oi.price) as total_amount
FROM ORDERS o
JOIN CUSTOMERS c ON o.customer_id = c.customer_id
JOIN ORDER_ITEMS oi ON o.order_id = oi.order_id
WHERE o.order_date >= CURRENT_DATE - 30
GROUP BY o.order_id, o.order_date, c.customer_name, c.email

SELECT
  customer_id,
  customer_name,
  email,
  phone,
  created_date,
  last_purchase_date
FROM CUSTOMERS
WHERE status = 'ACTIVE'

'--source_query': 'SELECT * FROM CUSTOMERS WHERE status = \'ACTIVE\''

# Read query from S3
import boto3
s3 = boto3.client('s3')
obj = s3.get_object(Bucket='<bucket>', Key='queries/customer-import.sql')
source_query = obj['Body'].read().decode('utf-8')

CREATE TABLE "glue_catalog"."sales"."customers" (
  customer_id BIGINT,
  customer_name STRING,
  email STRING,
  phone STRING,
  status STRING,
  credit_limit DECIMAL(10,2),
  created_date TIMESTAMP,
  last_purchase_date TIMESTAMP,
  load_timestamp TIMESTAMP,  -- Added: track when record was loaded
  load_date DATE             -- Partition column for efficient queries
)
PARTITIONED BY (load_date)
TBLPROPERTIES ('table_type' = 'ICEBERG')

I've mapped the Oracle CUSTOMERS table to this Iceberg schema:

Source (Oracle)                  → Target (Iceberg)
CUSTOMER_ID (NUMBER(10))         → customer_id (BIGINT)
CUSTOMER_NAME (VARCHAR2(200))    → customer_name (STRING)
EMAIL (VARCHAR2(255))            → email (STRING)
PHONE (VARCHAR2(20))             → phone (STRING)
STATUS (VARCHAR2(20))            → status (STRING)
CREDIT_LIMIT (NUMBER(10,2))      → credit_limit (DECIMAL(10,2))
CREATED_DATE (DATE)              → created_date (TIMESTAMP)
LAST_PURCHASE_DATE (DATE)        → last_purchase_date (TIMESTAMP)

Added columns:
- load_timestamp (TIMESTAMP): Tracks when record was loaded
- load_date (DATE): Partition column for efficient queries

Does this look correct? Any adjustments needed?

json_data STRING

metadata STRUCT<
  key1: STRING,
  key2: INT,
  key3: ARRAY<STRING>
>

tags ARRAY<STRING>

tags STRING  -- "tag1,tag2,tag3"

document BINARY

document_s3_key STRING  -- "s3://docs-bucket/doc123.pdf"