20-11-2020 01:06 AM
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
Solved! Go to Solution.
23-11-2020 09:48 AM
@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
20-11-2020 11:37 AM - edited 20-11-2020 02:42 PM
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
20-11-2020 02:57 PM
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!
20-11-2020 08:55 AM
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?
20-11-2020 10:27 PM
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?
23-11-2020 09:48 AM
@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
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In