Airflow Import Local csv file error

Contributor

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

 

1 ACCEPTED SOLUTION

Community Manager
Community Manager

@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

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

View solution in original post

5 REPLIES 5

Contributor

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

 

 

 

Community Manager
Community Manager

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! 

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Community Manager
Community Manager

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?

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Contributor

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?

 

Community Manager
Community Manager

@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

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

View solution in original post