Using '//N' null-string argument results in '/N' string value in HIVE table using Sqoop Operator in Airflow

user_13372880 0
Viewed 399

I am trying to use a sqoop to tranfer some data using the Airflow Sqoop Operator. This data contains empty values for which I would like them to end up as NULL in my HIVE table.

sqoop_operator_task = SqoopOperator(
    task_id = table[0],
    conn_id = "JDBC_OPA",
    table = table_name,
    cmd_type = "import",
    target_dir = "/sourcedata/sqoop_tmp/"+table_name,
    num_mappers = 1,
    extra_import_options = {
        'delete-target-dir' : "",
        'fields-terminated-by' : r'"\001"',
        'hive-database' : database_name,
        'hive-table' : table[0],
        'hive-delims-replacement' : "' '",
        'hive-import' : "",
        'hive-overwrite' : "",
        'null-non-string' : r"'\\N'",
        'null-string' : r"'\\N'"
    },
    dag = dag
)

Using the r"'\\N'" for the null-non-string & null-string results in the following command taken from Airflow log:

sqoop import --username SELECT_OPA_DSC --password MASKED --num-mappers 1 --connect MASKED --target-dir /sourcedata/sqoop_tmp/OWNER_DMK.DMK_AVY --as-textfile --delete-target-dir --fields-terminated-by "\001" --hive-database test_db --hive-table DMK_AVY --hive-delims-replacement ' ' --hive-import --hive-overwrite --null-non-string '\\N' --null-string '\\N' --table OWNER_DMK.DMK_AVY

This is according to the Sqoop documentation how it should be formatted to end up with NULL. However, I end up with '\N' in my table. Executing the full command directly in shell however, DOES give me the correct NULL values in the HIVE table.

I guess the issue lays in the difference of the HIVE query that is produced by the Sqoop job. HIVE query when command executed from Airflow:

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\001' LINES TERMINATED BY '\\012' STORED AS TEXTFILE

HIVE query when command executed from Shell directly:

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' LINES TERMINATED BY '\012' STORED AS TEXTFILE

Please advice me on a scenario, to alter my sqoop operator so that the HIVE query generated in Airflow is the same as directly using the command from Shell and thus producing NULL results in the HIVE table. Just removing one backslash does not work.

More info on the sqoop operator and hook here.

sqoop airflow hive shell python
1 Answers

I found a solution. Passing:

        'null-non-string' : r'\\N',
        'null-string' : r'\\N'

ends up with the argument being passed as --null-non-string \\N in the command and eventually giving NULL results in HIVE. However, passing the argument like this directly in shell raises an illegal escape character error... So somewhere there is a difference in how the command is processed in shell.

user_133728800
Related