Is it possible to query data from s3 directly?

[Xuanwo]

Hi, databenders.

I want to know if it is possible to query data from s3 directly?

Take presto for example (presto depends on the hive for this feature)

CREATE TABLE IF NOT EXISTS hive.iris.iris_parquet (
  sepal_length DOUBLE,
  sepal_width  DOUBLE,
  petal_length DOUBLE,
  petal_width  DOUBLE,
  class        VARCHAR
)
WITH (
  external_location = 's3a://iris/iris_parquet',
  format = 'PARQUET'
);

Then, we can query the data directly without COPY or STAGE:

SELECT 
  sepal_length,
  class
FROM hive.iris.iris_parquet 
LIMIT 10;

[BohuTANG]

Absolutely!
After COPY command done let's plan it.

[BohuTANG]

Also, I think they are still doing different things and targeting different scenarios:

1. COPY transforms the CSV/Parquet file format to FUSE engine, it will faster the query, aims for the large volume data.
2. Query data from S3 directly is a slow way, suites for small file or many pieces small files with Parquet format.

[sundy-li]

Copy means we must have a table to store the data.

Sometimes user just want to head small rows of the file for quick view, we can have a S3 table Engine, but this looks similar to Stage.

Stage is just a config manager to store configs like external_location = 's3a://iris/iris_parquet', format = Parquet ...

How about having select from stage(....), a stage table function.

[Xuanwo]

How about having select from stage(....), a stage table function.

select from stage(...) seems to lack catalog data. Do you mean we will have a CREATE TABLE (...) FROM STAGE(xxx), after that user can query from this table?

[sundy-li]

1. Data Type Inference from data files
2. Specific the schema as table function args or inside the stage config.

[BohuTANG]

How about having select from stage(....), a stage table function.

select from stage(...) seems to lack catalog data. Do you mean we will have a CREATE TABLE (...) FROM STAGE(xxx), after that user can query from this table?

We can get the schema if the file is parquet.
The important thing is that, make a external location read as a processor, like a ExternalScan, I am working on it.

[jiangzhe]

It looks similar to Hive feature "EXTERNAL TABLE". IMO, Define the schema via CREATE TABLE statement before the query could have advantages that direct scan cannot provide, e.g. additional information like column type and length, nullable, uniqueness, etc, which affacts query optimization.

[BohuTANG]

That's right, @sundy-li

[BohuTANG]

I am working on it in this PR: #4266

1. S3 will be an external table
2. Parse s3:// statement to a ReadSourcePlan
3. Pipeline execute and stream

It will be used for COPY INTO t1 FROM 's3://' first, then easy to adapte to the SELECT * FROM @stage where ... or SELECT * FROM s3:// where ... statement.

In databend, every operator unit will be a processor, we can measure and profiling them in an unified way.

[BohuTANG]

Can we base on the stage(External and Internal) to do this? @sundy-li

Snowflake external table:
https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html#syntax

 [ WITH ] LOCATION = externalStage

[sundy-li]

Yes, I think we can query from stage with params as copy did.