Import local or remote CSV file from sftp connection in localhost

Padawan

Hi all,

Currently in Exasol, IMPORT and EXPORT of local files is not supported in subselect".

This is supported with remote import!

So, I am trying a workaround by creating a connection and then using a remote import.

 

Connection:

CREATE OR REPLACE CONNECTION SFTP_EXA_LOCAL TO 'http://sftp:1080';


Create a test table

CREATE or replace TABLE default.test_tbl (
ID DECIMAL(10),
NAME VARCHAR(100) UTF8
);

 

import data from local file → OK

IMPORT INTO default.test_tbl
FROM LOCAL CSV FILE '/home/tests/test.csv'
SKIP = 1
COLUMN SEPARATOR = '|'
COLUMN DELIMITER = ''
;

All Good, I get my CSV file in the table.

 

Although I'd like to select on the fly the CSV. But I can't figure out how to. 

This is my attempt /"work-around" :

Import local CSV with a remote connection to the localhost

SELECT ID, NAME
FROM (
  IMPORT INTO ( ID DECIMAL(10),  NAME VARCHAR(100))
  FROM CSV
  AT SFTP_EXA_LOCAL
  FILE '/home/tests/test.csv'
  SKIP = 1
  COLUMN SEPARATOR = '|'
  COLUMN DELIMITER = ''
);

 

I get the error:

[42636] ETL-5105: Following error occured while reading data from external connection [http://sftp:1080//home/tests/test.csv failed with error code=404 after 178 bytes.] (Session: 164325218849122015)


I had this working before, or something very similar. I don't see what I am missing now.

hope someone can give me a clue.

 

Thanks in advance

1 ACCEPTED SOLUTION

Team Exasol
Team Exasol

Hi,

seems like your connection string is wrong.
You put "http" in your connection what is not an sftp connection.
Error 404 is an http error for "File not found".

It should look like: 

CREATE OR REPLACE CONNECTION SFTP_EXA_LOCAL TO 'sftp://<ip-address><:port-if-not-default-22>';

Just tested and worked like a charm

View solution in original post

1 REPLY 1

Team Exasol
Team Exasol

Hi,

seems like your connection string is wrong.
You put "http" in your connection what is not an sftp connection.
Error 404 is an http error for "File not found".

It should look like: 

CREATE OR REPLACE CONNECTION SFTP_EXA_LOCAL TO 'sftp://<ip-address><:port-if-not-default-22>';

Just tested and worked like a charm

View solution in original post