'Glue-Spark transform to Postgres time data type

Postgres has a time data type. I am trying to insert rows into postgres from a glue job. Given the code:

applymapping1 = ApplyMapping.apply(frame = SelectFromCollection, mappings = ("event_time", "time", "event_time", "time")

So timestamp and date work just fine. But if the column is of type "time" it is failing with error: IllegalArgumentException: 'Invalid type name time'

Since this is py-spark it is not clear to me which output datatypes are supported.

pythonpostgresqlapache-sparkaws-glue


Solution 1:[1]

I faced similar issue and implementing a simple trick worked out for me Apache spark doesn't support time data type, spark has only TimestampType and DateType .As Glue Dynamic frame is wrapper around spark dataframe glue interprets time datatype values as timestamp data type and adds a default date to the source time value "1970–01–01 " which is the unix epoch start time , for example a value stored as 13:00:00 becomes "1970–01–01 13:00:00" in glue dynamic frame .

Try this

# Fetch the event_time of every DynamicRecord and extract time by removing default date '1970-01-01 '
def map_function(dynamicRecord):
    time = str(dynamicRecord["event_time"])
    dynamicRecord["event_time"] = time[11:19]
    return dynamicRecord

# Apply the function to all the DynamicRecord
mapping3 = Map.apply(frame = S3bucket_node1, f = map_function, transformation_ctx = "mapping3")


ApplyMapping_node2 = ApplyMapping.apply(
    frame=mapping3,
    mappings=[
          # add other columns
          ("event_time", "string", "event_time", "string"),
    ],
    transformation_ctx="ApplyMapping_node2",
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adnan

Related Questions