Setting the file. One moment.
Skill 54 · Ingesting Into Data Lake
Subchapter 54.14
references/jdbc-ingest.mdMarkdown6 KBView on GitHub
Move data from a JDBC source (Oracle, SQL Server, PostgreSQL, MySQL, RDS, Aurora, Redshift) into the data lake. Assumes a Glue connection exists. If it doesn’t, delegate to the connecting-to-data-source skill first.
connecting-to-data-source skill)creating-data-lake-table skill)aws glue get-connection --name <CONNECTION_NAME> --region <REGION>If the connection does not exist, stop and delegate to connecting-to-data-source.
Ask the user which tables, views, or custom SQL query. See jdbc-schema-discovery.md for crawler-based discovery, direct schema inspection, and custom SQL patterns.
| Intent | Strategy | Reference |
|---|---|---|
| One-time full load | Full scan, write once | glue-job-scripts.md full-refresh template |
| Recurring, append-only (events, logs) | Incremental append with watermark | incremental-loading.md |
| Recurring, mutable (customers, products) | Incremental upsert with MERGE | incremental-loading.md |
| Small dimension | Full refresh via createOrReplace() | glue-job-scripts.md |
If the target table doesn’t exist, delegate to creating-data-lake-table. Never create it inline.
Use the PySpark templates in glue-job-scripts.md and the job config guidance in glue-job-config.md.
Reference the Glue connection via job Connections property:
"Connections": {"Connections": ["<CONNECTION_NAME>"]}In the script, read via connection name – no credentials in code:
source_df = glueContext.create_dynamic_frame.from_options(
connection_type="jdbc",
connection_options={
"useConnectionProperties": "true",
"connectionName": args['connection_name'],
"dbtable": args['source_table']
}
).toDF()For large tables, read in parallel via Spark partitioning on a numeric column:
jdbc_conf = glueContext.extract_jdbc_conf(args['connection_name'])
source_df = spark.read.format("jdbc").options(
url=jdbc_conf["url"],
user=jdbc_conf["user"],
password=jdbc_conf["password"],
dbtable="<SCHEMA>.<TABLE>",
numPartitions=10,
partitionColumn="<numeric_column>",
lowerBound=1,
upperBound="<max_value>"
).load()Best practices:
partitionColumnnumPartitions = number of Glue workers × 2lowerBound/upperBound cover actual data rangeRetrieve credentials from the connection at runtime rather than hardcoding. See connecting-to-data-source credential-security.md (opens in a new tab) for IAM DB auth and Secrets Manager patterns.
Source-to-Iceberg type mappings for ingest. Apply via .cast() or column aliases in the Glue script.
| Oracle | Iceberg | Notes |
|---|---|---|
| VARCHAR2, CHAR | STRING | |
| NUMBER(p,s) | DECIMAL(p,s) | |
| NUMBER (no scale) | BIGINT | For integer values |
| DATE | TIMESTAMP | Oracle DATE includes time |
| TIMESTAMP | TIMESTAMP | |
| CLOB | STRING | |
| BLOB | BINARY |
| SQL Server | Iceberg | Notes |
|---|---|---|
| VARCHAR, NVARCHAR, CHAR | STRING | |
| INT, SMALLINT | INTEGER | |
| BIGINT | BIGINT | |
| DECIMAL, NUMERIC | DECIMAL(p,s) | |
| FLOAT, REAL | DOUBLE | |
| BIT | BOOLEAN | |
| DATE | DATE | |
| DATETIME, DATETIME2 | TIMESTAMP |
| PostgreSQL | Iceberg | Notes |
|---|---|---|
| VARCHAR, TEXT | STRING | |
| INTEGER, SMALLINT | INTEGER | |
| BIGINT | BIGINT | |
| NUMERIC, DECIMAL | DECIMAL(p,s) | |
| REAL | FLOAT | |
| DOUBLE PRECISION | DOUBLE | |
| BOOLEAN | BOOLEAN | |
| DATE | DATE | |
| TIMESTAMP, TIMESTAMPTZ | TIMESTAMP | |
| JSON, JSONB | STRING | Parse in Spark if needed |
| UUID | STRING |
| MySQL | Iceberg | Notes |
|---|---|---|
| VARCHAR, CHAR, TEXT | STRING | |
| INT, SMALLINT, TINYINT | INTEGER | TINYINT(1) is BOOLEAN |
| BIGINT | BIGINT | |
| DECIMAL | DECIMAL(p,s) | |
| FLOAT | FLOAT | |
| DOUBLE | DOUBLE | |
| DATE | DATE | |
| DATETIME, TIMESTAMP | TIMESTAMP | |
| JSON | STRING |
Same as PostgreSQL mappings. Redshift-specific additions:
SUPER -> STRING (serialize) or STRUCT (parse)GEOMETRY / GEOGRAPHY -> BINARY or STRINGIf the Glue job fails with a connection-related error (timeout, auth failure, driver not found, SSL handshake), delegate to connecting-to-data-source for troubleshooting. Do not attempt network or credential fixes in this skill.