tools/athena/loadtoathena.py
def construct_query(params: dict, field_info: dict) -> str:
    """
    returns the formatted create table statement.
    """

    query_str = "CREATE EXTERNAL TABLE IF NOT EXISTS "
    # concatenate table name
    query_str += f'`{params["db_name"]}`.`{params["table_name"]}` ('
    # concatenate table columns
    query_str += "\n "
    query_str += get_table_cols(
        params["input"]["datasetids"], field_info, params["input"]["data_format"]
    )
    query_str += " \n)"

    # set ROW FORMAT
    if params["input"]["data_format"] == "CSV":
        # https://docs.aws.amazon.com/athena/latest/ug/csv-serde.html
        query_str += "\nROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'"
        query_str += "\nWITH SERDEPROPERTIES ("
        # space-seperated
        query_str += "\n    'separatorChar' = ',',"
        # double quoted strings
        query_str += "\n    'quoteChar' = '\"', "
        # escape charater: \\
        query_str += "\n    'escapeChar' = '\\\\' )"

    if params["input"]["data_format"] == "JSON":
        query_str += "ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'"

    if params["input"]["data_format"] == "STRUCTURED":
        query_str += "\nROW FORMAT DELIMITED"
        # custom delimiter
        query_str += f'\n    FIELDS TERMINATED BY {params["input"]["data_delimiter"]}'
        # escape charater: \\
        query_str += "\n    ESCAPED BY '\\\\'"
        # lines
        query_str += "\n    LINES TERMINATED BY '\\n'"

    # set storage format
    query_str += "\nSTORED AS"
    query_str += "\n    INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'"
    query_str += "\n    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'"

    # set input location
    query_str += f'\nLOCATION \'{params["input"]["bucket_path"]}\''

    return query_str

    returns the formatted create table statement.
    
returns the formatted create table statement.
returns the formatted create table statement .
def construct_query(params: dict, field_info: dict) -> str:
    query_str = "CREATE EXTERNAL TABLE IF NOT EXISTS "
    query_str += f'`{params["db_name"]}`.`{params["table_name"]}` ('
    query_str += "\n "
    query_str += get_table_cols(
        params["input"]["datasetids"], field_info, params["input"]["data_format"]
    )
    query_str += " \n)"
    if params["input"]["data_format"] == "CSV":
        query_str += "\nROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'"
        query_str += "\nWITH SERDEPROPERTIES ("
        query_str += "\n    'separatorChar' = ',',"
        query_str += "\n    'quoteChar' = '\"', "
        query_str += "\n    'escapeChar' = '\\\\' )"
    if params["input"]["data_format"] == "JSON":
        query_str += "ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'"
    if params["input"]["data_format"] == "STRUCTURED":
        query_str += "\nROW FORMAT DELIMITED"
        query_str += f'\n    FIELDS TERMINATED BY {params["input"]["data_delimiter"]}'
        query_str += "\n    ESCAPED BY '\\\\'"
        query_str += "\n    LINES TERMINATED BY '\\n'"
    query_str += "\nSTORED AS"
    query_str += "\n    INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'"
    query_str += "\n    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'"
    query_str += f'\nLOCATION \'{params["input"]["bucket_path"]}\''
    return query_str
def construct_query ( params : dict , field_info : dict ) -> str : query_str = "CREATE EXTERNAL TABLE IF NOT EXISTS " query_str += f'`{params["db_name"]}`.`{params["table_name"]}` (' query_str += "\n " query_str += get_table_cols ( params [ "input" ] [ "datasetids" ] , field_info , params [ "input" ] [ "data_format" ] ) query_str += " \n)" if params [ "input" ] [ "data_format" ] == "CSV" : query_str += "\nROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'" query_str += "\nWITH SERDEPROPERTIES (" query_str += "\n    'separatorChar' = ','," query_str += "\n    'quoteChar' = '\"', " query_str += "\n    'escapeChar' = '\\\\' )" if params [ "input" ] [ "data_format" ] == "JSON" : query_str += "ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'" if params [ "input" ] [ "data_format" ] == "STRUCTURED" : query_str += "\nROW FORMAT DELIMITED" query_str += f'\n    FIELDS TERMINATED BY {params["input"]["data_delimiter"]}' query_str += "\n    ESCAPED BY '\\\\'" query_str += "\n    LINES TERMINATED BY '\\n'" query_str += "\nSTORED AS" query_str += "\n    INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'" query_str += "\n    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'" query_str += f'\nLOCATION \'{params["input"]["bucket_path"]}\'' return query_str
returns the formatted create table statement.
returns the formatted create table statement .
"""
    returns the formatted create table statement.
    """ # concatenate table name # concatenate table columns # set ROW FORMAT # https://docs.aws.amazon.com/athena/latest/ug/csv-serde.html # space-seperated # double quoted strings # escape charater: \\ # custom delimiter # escape charater: \\ # lines # set storage format # set input location
params
dict
field_info
dict