Support for Redshift 'copy from' syntax

[RickardCardell]

I would like to add some missing Redshift syntax support, namely 'COPY target FROM src'.
The syntax specification: https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html

The biggest change is that would allow a string literal as the source, e.g 's3://path/':
COPY target FROM 's3://some_path'

It can be discussed if having a source table being represented as a path is something that should be supported.

[reata]

I checked the PR and code is fine. As you said, the only thing that should be discussed is whether we'd want a file path (be it s3, hdfs, or just local file) as table.

In Spark world which I'm familiar with, we can also do things like "SELECT * FROM parquet.`examples/src/main/resources/users.parquet`" (https://spark.apache.org/docs/latest/sql-data-sources-load-save-functions.html#run-sql-on-files-directly), though it's rarely used. Creating a table pointing to a path via hive metastore is more of a natural thing to do. Anyway we don't officially support this but it won't throw exception either.

$ python -m sqllineage.cli -e 'insert into tab1 select * from parquet.`examples/src/main/resources/users.parquet`'
Statements(#): 1
Source Tables:
    examples/src/main/resources/users.parquet
Target Tables:
    <default>.tab1

Here examples/src/main/resources/users is identified as database while the trailing parquet identified as table, with heading parquet being ignored. Not so good even though the result looks fine. The ideal way for me is to create another Path data model and not mixed it with Table if we really want this. But that's more of a long term thing.

I guess in redshift world, "COPY tab1 FROM tab2" is far less used than "COPY tab1 FROM 's3://bucket/file/path'" and loading data from S3 is a regular thing to do? If not, I would prefer the string literal support being removed. Would you share some of your thought here?

[RickardCardell]

Thanks for your time

The ideal way for me is to create another Path data model and not mixed it with Table if we really want this. But that's more of a long term thing.

+1, I totally agree

I guess in redshift world, "COPY tab1 FROM tab2" is far less used than "COPY tab1 FROM 's3://bucket/file/path'" and loading data from S3 is a regular thing to do? If not, I would prefer the string literal support being removed. Would you share some of your thought here?

In fact, I've only seen live examples of COPY tab1 FROM 's3://bucket/file/path' and this is the recommended way by AWS to ingest data from S3 into Redshift. So I would assume if one uses Redshift, it's not unlikely that this command will be used.

However, I am definitely fine with waiting with support for this command, since it would be much prettier to have a Path model as you mentioned. I'll use my fork in the mean time :)

[reata]

Thanks for your understanding and patience. I'll close the PR and keep it issue open for future reference then.

[reata]

Yet some more COPY command specification from other cloud providers:

• Snowflake "COPY INTO" command reference: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html
• Microsoft T-SQL "COPY" command reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

And as an FYI, COPY command is not in ANSI-SQL standard yet.

[reata]

Previously we had an example on SparkSQL select from a directory path directly, here's another corresponding syntax for writing to a directory path:

• Spark INSERT OVERWRITE DIRECTORY reference: https://spark.apache.org/docs/latest/sql-ref-syntax-dml-insert-overwrite-directory.html

[reata]

Hi @RickardCardell , I'm revisiting this issue as we're working on a new feature to bring sqlfluff as a parser option to be the alternative to sqlparse.

In covering the existing test cases using new parser, we're meeting some problems with this test case you provided earlier:

COPY tab1 FROM tab2 -- this is unparsable

copy from S3 works

COPY tab1 FROM 's3://mybucket/mypath' -- this works

I checked the Redshift docs and also only see reference of COPY FROM S3. So I'm wondering whether COPY FROM another table is valid syntax in Redshift or not. Unfortunately I don't have a Redshift instance to test against. It would be great if you can help. Thank you.

[RickardCardell]

I can't execute it on my side currently, but I can't find any references COPY tab1 FROM tab2 of the queries running right now.
The docs is quite clear that only specific data-sources are valid, and I'm afraid that I don't recall the details of this issue. Imho, follow the docs :)