Airflow Import Local csv file error

[skarangi]

Hi,

I was trying this section https://docs.exasol.com/connect_exasol/workflow_management/apache_airflow.htm

With one change , I was running an import statement in sql file.

test.sql file code:-

IMPORT INTO COUNTRY FROM LOCAL CSV FILE '/home/vagrant/airflow/data/country.csv'
ENCODING = 'UTF-8'
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','
COLUMN DELIMITER = ''''
SKIP = 0
REJECT LIMIT 0;

My DAG Code:-

from airflow import DAG
from datetime import datetime,timedelta
from airflow.operators.jdbc_operator import JdbcOperator
from airflow.operators.dummy_operator import DummyOperator
tmpl_search_path = '/home/vagrant/airflow/templates/' 
default_args = {'owner': 'airflow','depends_on_past': False,'start_date': datetime(2020, 1, 1) ,'retries': 1, 'retry_delay': timedelta(minutes=2)}
with DAG(dag_id='Exasol_DB_Checks',schedule_interval= '@hourly',default_args=default_args,catchup=False,template_searchpath=tmpl_search_path) as dag:
      start_task=DummyOperator(task_id='start_task',dag=dag)
      
      sql_task_1 = JdbcOperator(task_id='sql_cmd',
                                jdbc_conn_id='Exasol_db',
                                sql="test.sql",
                                autocommit=True,
                                params={"my_param":"{{ var.value.source_path }}"}
                                )
      start_task >> sql_task_1

Error:- pype._jclass.SQLException: java.sql.SQLException: Feature not supported: IMPORT and EXPORT of local files is only supported via JDBC (except prepared statements) or EXAplus1

Why is this error and is the work around for this?

Thanks,
Sreekanth

[exa-Nico]

Hi @skarangi is Airflow sending prepared statements to the database? What happens when you replace the IMPORT statement with a simple 'SELECT 1'? 

You can tell if a prepared statement is sent in Auditing if you see two entries for the statement (one PREPARE, one EXECUTE). 

Is this the case?

[skarangi]

Hi @exa-Nico ,

SELECT '1'; is executing without any issue.

Attached files contain the output when I try to run IMPORT Local option.

https://drive.google.com/drive/folders/1U-AQeLt4kmEi4mccV7z6OdAW85feElYo?usp=sharing

[exa-Nico]

Hi @skarangi ok the EXA_SQL_LAST_DAY confirmed it - the tool is sending a prepared statement, and LOCAL CSV is not supported in prepared statements. 

You could then import a csv from a non-local location, like an FTP server, S3 bucket, etc. This should not have any limitations. 

Let me know if that works! 

[skarangi]

Hi @exa-Nico ,

FTP connection worked. I did not understand the point from your previous reply. Airflow sending PREPARED statements and LOCAL option is not supported ( also suggested in documentation). How a prepared statement different from regular SQL?

[exa-Nico · Accepted solution]

@skarangi a prepared statement is a more "protected" SQL statement where the query is parsed and compiled, and then also accepts certain variables. For example:

INSERT INTO TEST.TABLE1 VALUES (?,?,?) 

The question marks are parameters that are sent after the query is compiled.

More info: java - Difference between Statement and PreparedStatement - Stack Overflow

So it looks like Apache Airflow is executing every query as if it is a prepared statement. I'm not sure if you are able to influence it at all, but IMPORT FROM LOCAL CSV won't work with prepared statements, so that's why you go the error message