anand086

anand086

anand086

anand086

admin@fleetdb # CREATE TABLE pg_glue_src (id serial not null, data jsonb, name text, class text);
CREATE TABLE
Time: 9.611 ms
admin@fleetdb # CREATE TABLE pg_glue_trgt (id serial not null, data jsonb, name text, class int);
CREATE TABLE
Time: 13.034 ms
admin@fleetdb # \d pg_glue_src
                              Table "admin.pg_glue_src"
+--------+---------+-----------+----------+-----------------------------------------+
| Column |  Type   | Collation | Nullable |                 Default                 |

anand086

admin@fleetdb # insert into pg_glue_src values (1, '{"id": "1234", "version": "1", "receiver": "100", "billing_date": "20210605", "account": "11111111"}', 'joe', null);
INSERT 0 1
Time: 2.263 ms
admin@fleetdb # insert into pg_glue_src values (2, '{"id": "2345", "version": "1", "receiver": "110", "billing_date": "20210604", "account": "11112222"}', 'john', '1');
INSERT 0 1
Time: 2.026 ms
admin@fleetdb # insert into pg_glue_src values (3, '{"id": "3456", "version": "2", "receiver": "120", "billing_date": "20210604", "account": "33331111"}', 'mark', '2');
INSERT 0 1
Time: 1.973 ms
admin@fleetdb # insert into pg_glue_src values (4, '{"id": "4567", "version": "3", "receiver": "130", "billing_date": "20210606", "account": "11444411"}', 'thomas', '3');

anand086

import sys
import os
import logging
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark import SparkConf

anand086

logger.info("Target connection options")
connection_aurora_options = {
    "url": db_url,
    "user": db_username,
    "password": db_password,
    "database": "fleetdb",
    "dbtable": "admin.pg_glue_trgt",
    "stringtype":"unspecified",  ##required to resolve error
    }
    

anand086

admin@fleetdb # select * from pg_glue_trgt;
+----+------------------------------------------------------------------------------------------------------+--------+-------+
| id |                                                 data                                                 |  name  | class |
+----+------------------------------------------------------------------------------------------------------+--------+-------+
|  1 | {"id": "1234", "account": "11111111", "version": "1", "receiver": "100", "billing_date": "20210605"} | joe    |  NULL |
|  3 | {"id": "3456", "account": "33331111", "version": "2", "receiver": "120", "billing_date": "20210604"} | mark   |     2 |
|  2 | {"id": "2345", "account": "11112222", "version": "1", "receiver": "110", "billing_date": "20210604"} | john   |     1 |
|  4 | {"id": "4567", "account": "11444411", "version": "3", "receiver": "130", "billing_date": "20210606"} | thomas |     3 |
+----+-------------------------------------------------------------------------------------

anand086

anand086

import sys
import os
import logging

from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.dynamicframe import DynamicFrame
from awsglue.job import Job