exasol / virtual-schema-common-document-files

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

Virtual Schema for Files

Virtual Schema Common Document Files allows you to query data stored in a document file in the same way as if the data was stored in a regular Exasol database table.

This module is part of a larger project called Virtual Schemas covering document based dialects as well as JDBC based, see complete list of dialects.

Document-based virtual schemas are characterized by

• a storage that is basically a container hosting the document files and also defining the access control and type of account needed to access the files and
• a document type defining the format of the document containing the data.

Storage Variants

You cannot directly use this adapter. Please, use one of the dialects for specific storage variants below.

• AWS S3
• Exasol BucketFS
• Google Cloud Storage
• Azure Blob Storage
• Azure Data Lake Storage Gen2

If this list does not contain your file source you can implement your own file source.

Document Types

Each storage variant can contain documents using any of the following supported document types:

• JSON
• JSON-Lines (one json document per line)
• Parquet
• CSV

You can also add support for other document types.

Integration Tests

VSDF builds and publishes a test-jar with common integration tests for document-oriented virtual schemas that can be used by any derived virtual schema. The derived virtual schema only needs to extend class com.exasol.adapter.document.files.AbstractDocumentFilesAdapterIT to inherit all common integration tests.

Performance Regression Tests

AbstractDocumentFilesAdapterIT also contains performance regression tests tagged with regression.

Changes in Performance Regression Tests

The following changes to the performance regression tests might influence comparability of test results:

• Version 7.3.1
  • CSV tests now use all six data types (string, boolean, integer, double, date and timestamp) instead of only string. The column count is unchanged.
  • Test names in the test report changed. They now use suffix () instead of (TestInfo).

Additional Information

• User Guide
• Changelog
• Dependencies

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

Initial implementation: Loading from json-lines file from BucketFS

Improve error message on syntax errors in data

Add more tests

Hidden columns can cause crash

Currently using Virtual Schema columns only in ORDER BY but not in the select list can cause a database crash.

Example:

SELECT ID FROM BOOKS ORDER BY PRICE;

Workaround:

Avoid having such columns only in ORDER BY by adding them to the select list in a nested statement.

SELECT ID FROM (SELECT ID, PRICE FROM BOOKS ORDER BY PRICE);

Root cause:

This is caused by SPOT-11018 (internal). Once this is fixed, activate the relevant test marked with SPOT-11018.

Add toDecimal and toJson mapping

CSV file type support

Add support for CSV files

Dependency check fails

Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project virtual-schema-common-document-files: Detected 1 vulnerable components:
Error:    ch.qos.reload4j:reload4j:jar:1.2.18.3:compile; https://ossindex.sonatype.org/component/pkg:maven/ch.qos.reload4j/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * 1 vulnerability found (8.6); https://ossindex.sonatype.org/vulnerability/sonatype-2022-5401

Escape char for selection with like is always \

The like statement allows the user to select the escape char: https://docs.exasol.com/db/latest/sql_references/predicates/not_like.htm
However, the virtual schema currently always uses \ as escape char independent of the one selected by the user.

Add regression test recording

Add a tool that records the execution time of the regression tests.

Add regression test

Add regression test for monitoring performance cross releases

Improve performance of loading many small files

Improve performance of loading many small files

Consider using jmh for regression tests

Maybe https://github.com/openjdk/jmh could be a good replacement for the custom logger

Empty json files cause exception

Allow overriding of document type detected by ending

In some cases the files might have a non-standard ending. The virtual schema could not load them since it can't detect the ending.

Solution:
Add way to set the type manually.

Add more tests for parquet types

Add more tests for parquet types

Update all dependent virtual schemas for document files dialects

Rename FileLoader to RemoteFileFinder

the name is confusing

Use virtual-schema-common-document 2.0.0

Move performance-test-recorder to its own repo

Move performance test-recorder to its own repo

Add Abstract DataLoaderFactory

Improve parquet-regression tests

Improve parquet-regression tests

Improve performance by splitting big parquet files

Improve performance by splitting big parquet files

Add documentation for document type plugin development

Add abstract FilesDocumentFetcher

Add error codes

Add CSV support

Add support for reading CSV files

Update development resources

Parquet support

Support reading from parquet files

Upgrade dependencies

 Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.1.0:audit (default-cli) on project virtual-schema-common-document-files: Detected 1 vulnerable components:
Error:    org.postgresql:postgresql:jar:42.2.23.jre7:test; https://ossindex.sonatype.org/component/pkg:maven/org.postgresql/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1
Error:      * [CVE-2022-21724] pgjdbc is the offical PostgreSQL JDBC Driver. A security hole was found in the j... (9.8); https://ossindex.sonatype.org/vulnerability/0f319d1b-e964-4471-bded-db3aeb3c3a29?component-type=maven&component-name=org.postgresql.postgresql&utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1

Extract file backends as separate repositories

Fix vulnerabilites in dependencies

[ERROR] Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project virtual-schema-common-document-files: Detected 10 vulnerable components:
[ERROR]   com.squareup.okhttp:okhttp:jar:2.7.5:compile; https://ossindex.sonatype.org/component/pkg:maven/com.squareup.okhttp/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2021-0341] CWE-295: Improper Certificate Validation (7.5); https://ossindex.sonatype.org/vulnerability/CVE-2021-0341?component-type=maven&component-name=com.squareup.okhttp%2Fokhttp&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [sonatype-2018-0035] CWE-20: Improper Input Validation (5.9); https://ossindex.sonatype.org/vulnerability/sonatype-2018-0035?component-type=maven&component-name=com.squareup.okhttp%2Fokhttp&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   io.netty:netty-common:jar:4.1.72.Final:test; https://ossindex.sonatype.org/component/pkg:maven/io.netty/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2022-24823] CWE-668: Exposure of Resource to Wrong Sphere (5.5); https://ossindex.sonatype.org/vulnerability/CVE-2022-24823?component-type=maven&component-name=io.netty%2Fnetty-common&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   org.apache.hadoop:hadoop-common:jar:3.3.1:compile; https://ossindex.sonatype.org/component/pkg:maven/org.apache.hadoop/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2022-26612] CWE-22: Improper Limitation of a Pathname to a Restricted Directory ('Path Traversal') (9.8); https://ossindex.sonatype.org/vulnerability/CVE-2022-26612?component-type=maven&component-name=org.apache.hadoop%2Fhadoop-common&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   com.google.guava:guava:jar:31.0.1-jre:compile; https://ossindex.sonatype.org/component/pkg:maven/com.google.guava/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [sonatype-2020-0926] CWE-379: Creation of Temporary File in Directory with Incorrect Permissions (6.2); https://ossindex.sonatype.org/vulnerability/sonatype-2020-0926?component-type=maven&component-name=com.google.guava%2Fguava&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   io.netty:netty-handler:jar:4.1.72.Final:test; https://ossindex.sonatype.org/component/pkg:maven/io.netty/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [sonatype-2020-0026] CWE-300: Channel Accessible by Non-Endpoint ('Man-in-the-Middle') (6.5); https://ossindex.sonatype.org/vulnerability/sonatype-2020-0026?component-type=maven&component-name=io.netty%2Fnetty-handler&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   org.apache.xmlrpc:xmlrpc-common:jar:3.1.3:test; https://ossindex.sonatype.org/component/pkg:maven/org.apache.xmlrpc/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2016-5003] CWE-502: Deserialization of Untrusted Data (9.8); https://ossindex.sonatype.org/vulnerability/CVE-2016-5003?component-type=maven&component-name=org.apache.xmlrpc%2Fxmlrpc-common&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2016-5002] CWE-611: Improper Restriction of XML External Entity Reference ('XXE') (7.8); https://ossindex.sonatype.org/vulnerability/CVE-2016-5002?component-type=maven&component-name=org.apache.xmlrpc%2Fxmlrpc-common&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   com.google.protobuf:protobuf-java:jar:2.5.0:compile; https://ossindex.sonatype.org/component/pkg:maven/com.google.protobuf/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2021-22569] CWE-400: Uncontrolled Resource Consumption ('Resource Exhaustion') (5.5); https://ossindex.sonatype.org/vulnerability/CVE-2021-22569?component-type=maven&component-name=com.google.protobuf%2Fprotobuf-java&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   commons-codec:commons-codec:jar:1.11:compile; https://ossindex.sonatype.org/component/pkg:maven/commons-codec/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1
[ERROR]     * [sonatype-2012-0050] CWE-20: Improper Input Validation (5.3); https://ossindex.sonatype.org/vulnerability/sonatype-2012-0050?component-type=maven&component-name=commons-codec%2Fcommons-codec&utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1
[ERROR]   org.apache.xmlrpc:xmlrpc-client:jar:3.1.3:test; https://ossindex.sonatype.org/component/pkg:maven/org.apache.xmlrpc/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [CVE-2016-5004] CWE-400: Uncontrolled Resource Consumption ('Resource Exhaustion') (6.5); https://ossindex.sonatype.org/vulnerability/CVE-2016-5004?component-type=maven&component-name=org.apache.xmlrpc%2Fxmlrpc-client&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]   com.google.code.gson:gson:jar:2.2.4:compile; https://ossindex.sonatype.org/component/pkg:maven/com.google.code.gson/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
[ERROR]     * [sonatype-2021-1694] CWE-502: Deserialization of Untrusted Data (7.5); https://ossindex.sonatype.org/vulnerability/sonatype-2021-1694?component-type=maven&component-name=com.google.code.gson%2Fgson&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Selections on SOURCE_REFERENCE using LIKE

S3 file backend

Selection on SOURCE_REFERENCE column

Improve small file loading by considering file size

Improve RemoteFilePrefetchingIterator#fillBuffer()

Add Virtual Schema for Files dialects

Virtual Schema uses too many workers for small amount of files

If only a few files e.g: 2 are loaded the virtual schema still uses all available workers. Starting them is a huge overhead and leads to a bad performance.

Workarround

set the property MAX_PARALLEL_UDFS = 1.
This wil force the virtual-schema to only use one worker.

Example:

CREATE VIRTUAL SCHEMA FILES_VS_TEST USING ADAPTER.S3_FILES_ADAPTER WITH
    CONNECTION_NAME = 'S3_CONNECTION'
    MAPPING         = '/bfsdefault/default/path/to/mappings/in/bucketfs'
   MAX_PARALLEL_UDFS = 1;

Replace javax.json by jakarta.json

Replace javax.json by jakarta.json

Make file type matching caseinsensitive

Add user-guide

Create abstract integration test

Move the test integration test from bucketfs-virtual-schema, since they test the code from this repository

Add column with file name

Make file types extendable

Document how a query can filter on SOURCE_REFERENCE

Document how a query can filter on SOURCE_REFERENCE

Add missing project keeper modules

missing modules:

• integration_tests
• udf_coverage

Fix unstable testFilterWithOrOnSourceReferenceWithBugfixForSPOT11018

The test testFilterWithOrOnSourceReferenceWithBugfixForSPOT11018 is unstable in the CI build.

Tests interfer

Currently the tests influence each other since the files from BucketFS are not cleaned before each test run.

Solution:

Add timestamp prefix to test files

Cleanup dependencies

Support for multiple files

Use case: Each document has it's own file.

Improve perfoamnce by distributing small amounts of files explicitly

Improve performance by distributing small amounts of files explicitly.

Hash partitioning is good for large amounts of files. For few files, however, it's not so good.