exasol / s3-document-files-virtual-schema

Virtual Schema for document files on AWS S3

Virtual Schema for Document Files in S3

This Virtual Schemas allows you to access document files stored in S3 like any regular Exasol table.

This Virtual Schema is built for and tested with the official AWS S3. Third-party S3 API compatible products are expected to work as well. It is highly recommended to thoroughly test 3rd party products used in combination with Exasol, especially regarding sufficient S3 API compatibility.

For MinIO each release of VSS3 is verified by automated integration tests. MinIO is a high-performance, S3 compatible object store. It is built for large scale AI/ML, data lake and database workloads. It runs on-prem and on any cloud (public or private) and from the data center to the edge. MinIO is software-defined and open source under GNU AGPL v3. See the Hand-On Guide for a quick tour with some sample JSON files.

This Virtual Schema is prepared for Java UDF startup time improver.

For supported document file formats see Virtual Schema Common Document Files.

Additional Information:

• User Guide
• Hands-on Guide (JSON files)
• Hands-on Guide (Parquet files)
• Hands-on Guide (MinIO with JSON files)
• Changelog
• Dependencies
• Developer Guide

s3-document-files-virtual-schema's Issues

Can we query files in private S3 "custom endpoints" without dns style bucket level access

Currently when we query an internal S3 compatible store such as minio, we get bucket string exceptions. The syntax does not allow for custom endpoints with path style access and without s3 in the bucket string. Thus we cannot use this with custom internal endpoints. Is there any means to enable path style access (bucket is not visible in dns but is first url part after domain name) or relax the requirement on the url format:

Exception / Stacktrace from query: SELECT * FROM FILES_VS_TEST."metrics" LIMIT 10

SQL-Error [22002]: VM error: F-UDF-CL-LIB-1126: F-UDF-CL-SL-JAVA-1006: F-UDF-CL-SL-JAVA-1026:
com.exasol.ExaUDFException: F-UDF-CL-SL-JAVA-1068: Exception during singleCall adapterCall
java.lang.IllegalArgumentException: E-S3VS-1: The given S3 Bucket string 'https://<private_endpoint>/test/metrics' has an invalid format. Expected format: http(s)://BUCKET.s3.REGION.amazonaws.com/KEY or http(s)://BUCKET.s3.REGION.CUSTOM_ENDPOINT/KEY. Note that the address from the CONNECTION and the source are concatenated. Change the address in your CONNECTION and the source in your mapping definition.
com.exasol.adapter.document.files.S3Uri.fromString(S3Uri.java:58)
com.exasol.adapter.document.files.S3FileLoader.(S3FileLoader.java:37)
com.exasol.adapter.document.files.S3FileLoaderFactory.getLoader(S3FileLoaderFactory.java:15)
com.exasol.adapter.document.files.FilesDocumentFetcherFactory.buildSegmentDescriptions(FilesDocumentFetcherFactory.java:59)
com.exasol.adapter.document.files.FilesDocumentFetcherFactory.buildDocumentFetcherForQuery(FilesDocumentFetcherFactory.java:39)
com.exasol.adapter.document.files.FilesQueryPlanner.planQuery(FilesQueryPlanner.java:33)
com.exasol.adapter.document.DocumentAdapter.runQuery(DocumentAdapter.java:122)
com.exasol.adapter.document.DocumentAdapter.pushdown(DocumentAdapter.java:107)
com.exasol.adapter.AdapterCallExecutor.executePushDownRequest(AdapterCallExecutor.java:109)
com.exasol.adapter.request.PushDownRequest.executeWith(PushDownRequest.java:55)
com.exasol.adapter.AdapterCallExecutor.executeAdapterCall(AdapterCallExecutor.java:26)
com.exasol.adapter.RequestDispatcher.processAdapterCall(RequestDispatcher.java:48)
com.exasol.adapter.RequestDispatcher.adapterCall(RequestDispatcher.java:33)
com.exasol.ExaWrapper.runSingleCall(ExaWrapper.java:100)
(Session: 1718395300191535104)

Support MFA in integration tests

Support MFA in integration tests

Auto-inference for Parquet files

See exasol/virtual-schema-common-document#155

Use system properties to set DEBUG_ADDRESS and LOG_LEVEL

IntegrationTestSetup.java sets constant values for DEBUG_ADDRESS and LOG_LEVEL.

properties.put("DEBUG_ADDRESS", "127.0.0.1:3001");
properties.put("LOG_LEVEL", "ALL");

Please consider to remove these lines and propose using the system properties supported by exasol/test-db-builder-java#103 in the documentation (either user_guide or developer_guide).

Move FilteringIterator to virtual-schema-common-document

Move FilteringIterator to virtual-schema-common-document

Rationale:
All other iterators are there. Would be good to have them in one place.

Remove upload driver part from user guide

Situation

The part with "Upload the driver to BucketFS" can be confusing, since in other VS we require 3rd party driver to be uploaded. But it is not required for VS S3.

We should change it to "Access Files in BucketFS".

Acceptance Criteria

• User guide is updated

Validate that awsEndpointOverride starts without protocol

It must not start with http:// or https://

Add performance test for few big parquet files

Add performance test for few big parquet files

Add hands-on guide

Add a hands-on guide

Connection string documentation is confusing

in the hands-on guide it's unclear what key means.

Add support for Extension Manager

Add an extension for extension-manager that allows installing an S3 VS.

See pull requests:

• #84
• #88
• #90
• #91
• #92
• #93
• #94
• #95
• #96
• #100

Support dots in bucket name

According to the AWS S3 docs, AWS S3 supports dots in bucket names. Currently, that does not work with the S3 Virtual Schema.

Remove exclude for E-PK-CORE-53 dependencies.md file has outdated content

After setting required maven version to 3.8.7. or higher with PK ticket #444 (project-keeper release 2.9.6) users can remove the suppressed warning from file .project-keeper.yml:

  - regex: "(?s)E-PK-CORE-53: The dependencies.md file has outdated content.*"

Affected repositories:

• spark-connector
• ci-isolation-aws
• error-catalog
• cloudwatch-adapter
• tableau-connector
• parquet-io-java
• virtual-schema-common-document
• parquet-edml-generator
• cloudwatch-dashboard-examples
• glue-connector
• exasol-virtual-schema
• ✅ project-keeper
• s3-document-files-virtual-schema
• cloud-storage-extension
• extension-manager
• java-class-list-extractor
• edml-java

Release fails due to missing AWS secret

The integration tests of thi9s repository require that a AWS_Secret is defined. For that reason, the tests during release fail.

Add GitHub workflow to run regression tests

Add a GitHub workflow to run regression tests.

Test errors messages for wrong connection strings

Add test for error messages for incomplete S3 connection strings. Check if they are readable and if necessary improve messages.

Remove SQL_DIALECT property from documentation

The property is no longer required.
So lets remove it from the documentation.

Add class list generation

Add class list generation via https://github.com/exasol/java-class-list-extractor

Regression test broken

The regression tests broke with the last release.

Clarify in the user guide that the schema mapping definition must be uploaded

Just like in the hands on guide

Add a backup for performance test results

Also store the performance test resuts somewhere else then in the demodb.
Ideas:

• add to release
• uploasd to a s3-bucket

Add ci build for ciislation

The ci isolation is defined in java. Add a github action that tests the java code

Dependency upgrade

See log messages from build job Dependency Check:

• io.netty:netty-handler:jar:4.1.86.Final in runtime
  • CVE-2023-34462, severity CWE-770: Allocation of Resources Without Limits or Throttling (6.5)
• org.xerial.snappy:snappy-java:jar:1.1.8.3 in compile
  • CVE-2023-34453, severity CWE-190: Integer Overflow or Wraparound (7.5)
  • CVE-2023-34454, severity CWE-190: Integer Overflow or Wraparound (7.5)
  • CVE-2023-34455, severity CWE-770: Allocation of Resources Without Limits or Throttling (7.5)

Excluded vulnerabilities:

• [CVE-2020-36641] CWE-611: Improper Restriction of XML External Entity Reference ('XXE') (9.8); https://ossindex.sonatype.org/vulnerability/CVE-2020-36641?component-type=maven&component-name=fr.turri%2FaXMLRPC&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Add regression test for many small JSON files

We have good support there until now. By a test we want to make sure that we don't break it

Add category for extension

The extension interface adds support for specifying a category in version 0.2.0.

We must upgrade to the latest interface version and configure category doc-virtual-schema (document-based virtual schema).

See also exasol/extension-manager#100 and exasol/extension-manager-interface#42

Remove lombok

We should remove lombok from this project to reduce dependencies and complexity.

Auto-inference for CSV files

Auto inference for CSV files was implemented in exasol/virtual-schema-common-document-files#131 and exasol/virtual-schema-common-document-files#130 / virtual-schema-common-document-files 7.3.0.

Use Virtual-Schema-Common-Document-Files-3.0.0

Add by upgrading to new vs-common-document-files version

Allow updating installed extensions

In exasol/extension-manager-interface#44 we modified the API to allow updating installed extensions to a new version.

We need to implement this new feature for this virtual schema.

Adapt to new regression test setup

Uploading of the regression test result has changed. We need to adapt to the new process.

Performance regression tests fail

 │ Error: Your query returned no results. Please change your search criteria and try again.
Terraform planned the following actions, but then encountered a problem:
│ 
│   with module.exasol_setup.module.exasol.data.aws_ami.exasol,
│   on .terraform/modules/exasol_setup.exasol/main.tf line 1, in data "aws_ami" "exasol":
│    1: data "aws_ami" "exasol" {
│ 

See https://github.com/exasol/s3-document-files-virtual-schema/actions/runs/4486905592/jobs/7889815574

CVE-2020-25649 introduced by AWS-SDK

The dependency to the AWS SDK introduces CVE-2020-25649 into this project.

Vulnerabilities

• com.fasterxml.woodstox:woodstox-core:jar:5.3.0 in compile
  • CVE-2022-40152, severity CWE-787: Out-of-bounds Write (7.5)
• io.netty:netty-codec:jar:4.1.77.Final in runtime
  • CVE-2022-41915, severity CWE-113: Improper Neutralization of CRLF Sequences in HTTP Headers ('HTTP Response Splitting') (6.5)
• com.fasterxml.jackson.core:jackson-core:jar:2.14.0-rc1 in compile
  • sonatype-2022-6438: 1 vulnerability (7.5)
• org.yaml:snakeyaml:jar:1.33 in test
  • CVE-2022-1471, severity CWE-502: Deserialization of Untrusted Data (9.8)
• commons-net:commons-net:jar:3.6 in compile
  • CVE-2021-37533, severity CWE-20: Improper Input Validation (6.5)

Make cache bucket name configurable

Right now, it does not work in other accounts then our ci-test account

Is removal of SQL_DIALECT property also planned for this Virtual schema?

Use unified connection definition

Use the unified connection definition specified in: https://github.com/exasol/connection-parameter-specification/

Update performance regresson tests to use CSV data types

In exasol/virtual-schema-common-document-files#135 we updated performance regresson tests to use CSV data types. Now e need to upgrade https://github.com/exasol/virtual-schema-common-document-files to version 7.1.3.

Upade vs-common-files-dependency

Update to use virtual-schema-common-document-files 2.0.0

Fix security hotspot

fix sonar security hotspot:
https://sonarcloud.io/project/security_hotspots?id=com.exasol%3As3-document-files-virtual-schema

Fix upload of regression test results

The exaplus cli fails from time to time with

EXA: IMPORT INTO ***.REGRESSION_TEST_RESULTS  FROM LOCAL CSV FILE '...
Error: [04508] Connection for IMPORT INTO ***.REGRESSION_TEST_RESULTS FROM LOCAL CSV FILE './target/performanceLogWithReleaseName.csv' COLUMN SEPARATOR = ',' SKIP = 1; failed.

for example: https://github.com/exasol/s3-document-files-virtual-schema/runs/3998565329?check_suite_focus=true

Rename error codes from S3VS to VSS3

Rename error codes from S3VS to VSS3

Replace test config parsing library

Currently s3-document-files-virtual-schema parses test_config.yml with snakeyaml which has security vulnerabilities.
As the file format is quite simple we should replace snakeyaml with Java's property format.

Example:

awsProfile: default_mfa
owner: [email protected]
s3CacheBucket: user-testdata-cache

Proposal: change name of expected file to test_config.properties. In case this file does not exist and test_config.yml does exists then tests should fail with a helpful error message (e.g. "please migrate from yaml to properties").

Add cache for s3 uploads

The uploads to s3 are quite slow. Especially for local runs, when the upload goes over a DSL connection.

Solution:
Add a cache that only uploads the files if they changed

Add hands-on guide for parquet files

Add a hands-on guide for parquet files

Initial implementation

Adapt Extension to Exasol v8

The findInstances command reads the virtual schema table SYS.EXA_ALL_VIRTUAL_SCHEMAS. The schema of this table changes in Exasol v8, so we need to adapt the query to also work with Exasol 8.

Precondition: Exasol Docker DB v8 is available that allows running Java UDFs. Without this we can't run integration tests.

Unreadable error message when last slash is missing in connection string

If there is no trailing slash in the connection string, the virtual schema fails with a strange error message.

Add performance test

Unreadable error message if region is missing

If no region is specified the virtual-schema query fails with an unreadable error:

SQL Error [22002]: VM error: F-UDF-CL-LIB-1127: F-UDF-CL-SL-JAVA-1002: F-UDF-CL-SL-JAVA-1013: 
com.exasol.ExaUDFException: F-UDF-CL-SL-JAVA-1080: Exception during run 
software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.
software.amazon.awssdk.core.exception.SdkClientException$BuilderImpl.build(SdkClientException.java:98)
software.amazon.awssdk.awscore.interceptor.HelpfulUnknownHostExceptionInterceptor.modifyException(HelpfulUnknownHostExceptionInterceptor.java:59)
software.amazon.awssdk.core.interceptor.ExecutionInterceptorChain.modifyException(ExecutionInterceptorChain.java:199)
software.amazon.awssdk.core.internal.http.pipeline.stages.utils.ExceptionReportingUtils.runModifyException(ExceptionReportingUtils.java:54)
software.amazon.awssdk.core.internal.http.pipeline.stages.utils.ExceptionReportingUtils.reportFailureToInterceptors(ExceptionReportingUtils.java:38)
software.amazon.awssdk.core.internal.http.pipeline.stages.ExecutionFailureExceptionReportingStage.execute(ExecutionFailureExceptionReportingStage.java:39)
software.amazon.awssdk.core.internal.http.pipeline.stages.ExecutionFailureExceptionReportingStage.execute(ExecutionFailureExceptionReportingStage.java:26)
software.amazon.awssdk.core.internal.http.AmazonSyncHttpClient$RequestExecutionBuilderImpl.execute(AmazonSyncHttpClient.java:193)
software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.invoke(BaseSyncClientHandler.java:135)
software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.doExecute(BaseSyncClientHandler.java:161)
software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.lambda$execute$1(BaseSyncClientHandler.java:114)
software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.measureApiCallSuccess(BaseSyncClientHandler.java:169)
software.amazon.awssdk.core.internal

Migrate ci-isolation to AWS CDK v2

See https://docs.aws.amazon.com/cdk/v2/guide/migrating-v2.html for details