exasol / virtual-schemas Goto Github PK
View Code? Open in Web Editor NEWEntry point repository for the EXASOL Virtual Schemas
Home Page: http://www.exasol.com
License: MIT License
Entry point repository for the EXASOL Virtual Schemas
Home Page: http://www.exasol.com
License: MIT License
Problem
The Exasol JDBC Adapter for Virtual Schemas doesn't quote column names which are reserved keywords in the remote database. This causes queries to fail on the remote database, if the user selects a column named after a keyword but doesn't select all columns from the table (i.e. no SELECT * in the pushdown query).
-- In the remote database:
CREATE TABLE "MONTH"(
"YEAR" VARCHAR(50) UTF8,
"MONTH" VARCHAR(50) UTF8,
YEAR_MONTH DATE);
-- In the local database:
ALTER VIRTUAL SCHEMA VS REFRESH;
SELECT "MONTH" FROM VS.MONTH;
Background:
Currently, the integration test framework allows to run queries on virtual tables using executeQuery(), however, the returned ResultSets are never closed. Having too many ResultSets probably causes problems at some point (they already caused problems with the Teradata jdbc driver, when the JdbcMetadataReader did not close his ResultSets, which is now fixed).
Proposed Improvement:
The AbstractIntegrationTest framework should somehow remember the returned ResultSets and close them all automatically at some kind of tearDown method at the end of a test. Or we should find any other way to make sure that resultsets are closed as quickly as possible automatically.
In #75 some suggestions from the review were left for an extra refactoring branch.
Open review issues are:
Currently the Capabilities
object is mutable and has setters that look similar to builder methods. Turning the implementation to an immutable object with a dedicated builder is cleaner.
Capabilities.Builder
existsCapabilites
object can only be created through the builderCurrent status
In order to use datatypes of remote systems, that are not implemented in EXASOL, these types have to be casted. Currently these cast rules are hard coded for each dialect.
Proposed change
I would like to make these cast rules configurable at virtual schema creation time, so that the predefined rules can be altered and extended. This way new types (a database extension or module) can be integrated easily. Furthermore some cast rules are ambiguous (is an Oracle NUMBER best casted to VARCHAR or DECIMAL?) and could be easily adapted for the particular use case.
I found a few inaccuracies in the Virtual Schema API Documentation while using it.
Missing literal_interval
json example here: https://github.com/exasol/virtual-schemas/tree/master/doc#literal
I discovered that "type": "predicate_equals"
doesn't really work in our implementation, but "type": "predicate_equal"
works fine. We need to fix the documentation or the implementation. You can find it here: https://github.com/exasol/virtual-schemas/tree/master/doc#predicates
The same for "predicate_notequals"
.
The same for "predicate_lessequals"
.
Missing predicate_is_null
and predicate_is_not_null
here: https://github.com/exasol/virtual-schemas/tree/master/doc#predicates
The bigserial type (https://www.postgresql.org/docs/current/datatype-numeric.html) is not supported by the PostgreSQL dialect of the adapter and leads to an error.
The citext module adds a case-insensitive character type to PostgreSQL (https://www.postgresql.org/docs/current/citext.html).
This type is not supported by the PostgreSQL dialect of the adapter and leads to an error.
It's problematic to build and run the virtual-schemas project with the current project structure (the problems appeared in different IDE).
Every module should be moved to the separated repository and we are going to start with the "virtualschema-common" module.
You can find the "virtualschema-common" module here: https://github.com/exasol/virtual-schema-common
Error messages are not helpful with the current JDBC adapter (version 1.1.0), e.g.:
If the connection I am using in a virtual schema is broken, I get this error message:
[Code: 0, SQL State: 22002] VM error:
java.lang.Exception: Unexpected error in adapter for following request: {
"schemaMetadataInfo" :
{
"name" : "VIRTUAL_EXASOL",
"properties" :
{
"CONNECTION_NAME" : "EXASOLV6",
"EXA_CONNECTION_STRING" : "10.48.106.23:42627",
"IMPORT_FROM_EXA" : "true",
"SCHEMA_NAME" : "TEST_SCHEMA",
"SQL_DIALECT" : "EXASOL"
}
},
"type" : "createVirtualSchema"
}
Response:
Stack trace:
com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:70)
(Session: 1617565938970599020)
Which is totally useless and unclear without looking at the logfiles.
In version 1.0.1 I get a backtrace, which make the error message useful:
[Code: 0, SQL State: 22002] VM error:
java.lang.Exception: Unexpected error in adapter: Connection refused (Connection refused)
Stacktrace: com.exasol.jdbc.ConnectFailed: Connection refused (Connection refused)
at com.exasol.jdbc.AbstractEXAConnection.setupConnection(AbstractEXAConnection.java:1295)
at com.exasol.jdbc.AbstractEXAConnection.Connect(AbstractEXAConnection.java:1177)
at com.exasol.jdbc.AbstractEXAConnection.<init>(AbstractEXAConnection.java:435)
at com.exasol.jdbc.EXAConnection.<init>(EXAConnection.java:38)
at com.exasol.jdbc.EXADriver.connect(EXADriver.java:159)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:208)
at com.exasol.adapter.jdbc.JdbcMetadataReader.establishConnection(JdbcMetadataReader.java:88)
at com.exasol.adapter.jdbc.JdbcMetadataReader.readRemoteMetadata(JdbcMetadataReader.java:33)
at com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:110)
at com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:102)
at com.exasol.adapter.jdbc.JdbcAdapter.handleCreateVirtualSchema(JdbcAdapter.java:96)
at com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.exasol.ExaWrapper.runSingleCall(ExaWrapper.java:95)
For following request: {
"schemaMetadataInfo" :
{
"name" : "VIRTUAL_EXASOL",
"properties" :
{
"CONNECTION_NAME" : "EXASOLV6",
"EXA_CONNECTION_STRING" : "10.48.106.23:42627",
"IMPORT_FROM_EXA" : "true",
"SCHEMA_NAME" : "TEST_SCHEMA",
"SQL_DIALECT" : "EXASOL"
}
},
"type" : "createVirtualSchema"
}
Response:
Stack trace:
com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:90)
(Session: 1617565938970599020)
There are probably outdated places in documentation after removing common part from the project.
Need to review and fix it.
Document carefully AdapterNotes
: how this class works.
There are two places where you need to change the version of virtual schema common.
It's easy to forget about it, so it's better to bound them.
We moved a common module from this project to a separated project and refactored the code.
Cut off the common module from the project and use the common module from the separated one.
This interface method is only used in the Teradata dialect. There is a trivial default implementation in AbstractSqlDialect
.
Looking at the Teradata example I see that what this code intends to do is something different. The dialect should be able to decide not to iterate over columns of invalid views.
SqlDialect.handleException(...)
and all invocations.In some databases like postgresql it's possible to have more than one identity/autoincrement column per table.
As this is not allowed in EXASOL the exception
[04000] More than one identity column returned by adapter for table
is thrown.
To solve this rare situation, it would be great to have a global property like IGNORE_AUTOINCREMENT_CONSTRAINTS=true in the adapter definition.
Currently the precision of INTERVAL
data types is hard-coded in the Exasol SQL dialect. The necessary information is not available via JDBC.
Background:
Virtual schemas for Oracle data sources currently uses IMPORT FROM JDBC internally to retrieve the data. This limits performance and offers potential for improvement. Exasol has a built-in IMPORT FROM ORA
command to read data from an Oracle database, but this command is not yet used. This command has a performance advantage (implemented in C++ and can load in parallel) over the JDBC driver while providing the same features.
Proposed improvement:
To improve on the current performance situation, a new IMPORT_FROM_ORA
option will be introduced. When activated, the virtual schema will use the IMPORT FROM ORA
command to fetch data from the Oracle database. The JDBC connection will still be required to fetch meta data (e.g., schemas, table columns, etc.). Additionally an Oracle connection string will be needed which is used in IMPORT_FROM_ORA
.
This feature is inspired by the already existing IMPORT_FROM_EXA
property, which allows using the faster IMPORT FROM EXA
for Exasol data sources.
Link to the sonar's check results:
https://sonarcloud.io/dashboard?id=exasol_virtual-schema-common-java
It would be great to be able to add additional properties for an individual dialect.
For example when using db2 or other databases that have more detailed timestamp one could tell the dialect to cast the timestamps to varchar (when precision matters) or to leave it the way (truncation).
In this case it would also be great if the NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT Exasol Parameters could be accessed from within the dialect (-> casting to the correct format).
Regards
Charly
Amazon's AWS Redshift Spectrum allows accessing tableau files located on S3 through Redshift. Unfortunately the projections that are called "external tables" in Redshift's terminology are not visible through the regular metadata as provided by the Redshift JDBC driver.
Currently it looks like we have to merge the regular table metadata obtained through the JDBC driver with information extracted from special tables that store the structure of the "external tables".
SqlTestUtil exists in two places: test folder of this project and test folder of virtual-schema-common project.
We need to remove the duplication lately.
As a contributor,
I want to see an UML model of the Virtual Schema's top-level architecture
so that I can understand it quicker and more easily.
The oracle dialect handles timestamps differently depending on if you use a projection or not:
select * from docker_oravs.tstest;
-->
TS TSTZ TSLTZ
2013-09-11 12:55:05 2013-09-11 12:55:05 2013-09-11 19:55:05
select TS from docker_oravs.tstest;
-->
TS
11-SEP-13 12.55.05.123456 PM
The reason is that the projection adds TO_CHAR
for timestamps and hence applies the NLS_TIMESTAMP_FORMAT
.
Remove '@author' from java docs and write this information into a separated file.
Document dependencies, catagorized by module (common, dialect) and life cycle phase (built, run time, test):
Problem:
If a view is invalid in Exasol, the JDBC driver seems to return zero columns for this view.
Then the Adapter throws an com.exasol.adapter.metadata.MetadataException: A table without columns was encountered: ADDRESSBOOK_EXTENDED_T. This is not supported. Please check if this table has columns. If the table does have columns, the dialect probably does not properly handle the data types of the columns.
There might be other cases where a JDBC driver returns zero columns.
Workaround
One can use the TABLE_FILTER
property to select the tables or views which should be loaded.
Proposed Solution:
It should be investigated how EXCEPTION_HANDLING (currently only used for Teradata dialect) can be extended to be used for all dialects, so that the user has a better workaround than specifying every table to load.
As an administrator of many GitHub repositories,
I want to regularly safe repository metadata in my Exasol database
so that I can run analytics on the collected data.
Since this will go into the first version of the virtual schema dialect, we will focus on some basic metadata
We will use the following Java API (Licenses Apache 2.0 and MIT):
http://github-api.kohsuke.org
JUnit 5 is the successor of the JUnit 4 framework we are using at the moment. It has a better design, clearer life cycle handling and a uniform extension mechanism. In contrast JUnit 4 has two: Runners and Rules and they are not fully compatible.
I created the following adapter:
CREATE OR REPLACE JAVA ADAPTER SCRIPT adapter.jdbc_adapter_mssql AS
// This is the class implementing the callback method of the adapter script
%scriptclass com.exasol.adapter.jdbc.JdbcAdapter;
// This will add the adapter jar to the classpath so that it can be used inside the adapter script
// Replace the names of the bucketfs and the bucket with the ones you used.
%jar /buckets/bucketfs1/bucket_adapter/virtualschema-jdbc-adapter-dist-1.6.0.jar;
// You have to add all files of the data source jdbc driver here (e.g. Hive JDBC driver files)
%jar /buckets/bucketfs1/bucket_adapter/mssql-jdbc-7.2.1.jre8.jar;
/
Then I created a connection using SCHEMA_NAME = 'dbo'
. It works fine.
However, when I set SCHEMA_NAME = 'export'
, I get to following error message:
SQL Error [22002]: VM error:
com.exasol.adapter.AdapterException: Schema export does not exist. Available schemas: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin, dbo, guest, INFORMATION_SCHEMA, sys
Stack trace:
com.exasol.adapter.jdbc.JdbcMetadataReader.findSchema(JdbcMetadataReader.java:221)
com.exasol.adapter.jdbc.JdbcMetadataReader.readRemoteMetadata(JdbcMetadataReader.java:46)
com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:135)
com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:125)
com.exasol.adapter.jdbc.JdbcAdapter.handleCreateVirtualSchema(JdbcAdapter.java:118)
com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:44)
It looks like the adapter can only access the default MSSQL schemas like dbo, guest, sys, db_..., but not other created schemas.
As of version 1.1.0 of the virtual schemas Eclipse shows 24 warnings for the code.
I stumbled over a static helper class SqlTestUitl
. Apart from the fact that the name is poorly chosen, the contstructor is not hidden, it is not final and it resides in the main
source folder instead of test
.
test
*util*
is not acceptable)A few dialects are not finished yet.
They don't support capabilities and also contains a few TODO.
For example: MysqlSqlDialect
We should finish the work.
We need builder for SchemaAdapterNotes because constructor has too many arguments.
In exasol/virtual-schema-common-java#38 we introduce an update of the Capabilities
class that makes it immutable and provides a builder which makes the code more readable.
This issue here exists to integrate that change.
Steps:
CREATE SCHEMA adapter;
CREATE JAVA ADAPTER SCRIPT adapter.jdbc_adapter AS
%scriptclass com.exasol.adapter.jdbc.JdbcAdapter;
%jar /buckets/bucketfs/bfsdefault/default/virtualschema-jdbc-adapter-dist-0.0.1-SNAPSHOT.jar;
/
CREATE CONNECTION exasol_conn TO 'jdbc:exa:192.168.99.100:8899' USER 'sys' IDENTIFIED BY 'exasol';
CREATE VIRTUAL SCHEMA virtual_exasol USING adapter.jdbc_adapter WITH
SQL_DIALECT = 'EXASOL'
CONNECTION_NAME = 'EXASOL_CONN'
SCHEMA_NAME = 'default';
Result:
Error fired:
[22002] VM error: Internal error: VM crashed (Session: 1576534527788307527)
In preparation for making the Virtual Schemas more extensible, we introduced the RequestDispatcher
and RemoteLogManager
in virtual-schema-common-java
. In this ticket we are going to use them and remove the old code that statically dispatched to the JdbcAdapter
.
JdbcAdapter
gets requests from RequestDisptacher
JdbcAdapter
to the common moduleGenerating the IMPORT
that encapsulates the pushdown SQL is currently done in the JDBCAdapter
class. This does not make sense since it is dialect specific and bloats the JDBCAdapter
class.
Create an interface in the adapter module that created the SQL statement. Create an abstract class that generates the default. Have the code of the individual adapters override the default implementation where necessary (like suggested in review of #95)
If a connection is used in a Virtual Schema that stores username and password, this information should not be exposed in the resulting pushdown SQL. This works fine for JDBC connections, but not for the special ORA connections:
create connection ora_connection to '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orautf8)))' user 'foo' identified by 'bar';
create virtual schema oravs using adapter.jdbc_adapter
with connection_name = 'ora_jdbc_connection'
sql_dialect='ORACLE' schema_name = 'LOADER'
IMPORT_FROM_ORA = 'true'
ORA_CONNECTION_NAME = 'ora_connection';
Execution of explain virtual select x from oravs.t;
returns the pushdown:
IMPORT FROM ORA AT ora_connection USER 'foo' IDENTIFIED BY 'bar' STATEMENT 'SELECT * FROM "LS"."T"'
VS - virtual schema, source system is Oracle DB 12.2, jdbc driver ojdbc8.jar
The following query
select count(*) FROM VS.ONTIME_ALL WHERE
(C5 >= to_date('2009-05-01', 'YYYY-MM-DD') AND C5 < to_date('2009-05-31', 'YYYY-MM-DD')+1)
turns into
SELECT COUNT(*) FROM DWH.ONTIME_ALL WHERE C5 BETWEEN DATE '2009-05-01' AND DATE '2009-06-01'
on Oracle side.
There is no place where users can find information if they have an error message.
Create a handbook with possible errors, their descriptions and ways to handle these errors.
A String column in a Hive Table has type VARCHAR(255) in the Virtual Schema. The Hive Dialect just uses the JDBC metadata from Hive. It looks like Hive claims that the length of a String column is 255, although it can hold up to 2GB of data.
We will deprecate the use of the properties CONNECTION_STRING, USERNAME and PASSWORD.
Using these properties the username and password are exposed through the virtual schema requests and responses. It is highly recommended to encapsulate this data in a dedicated connection object.
Throw an error if these properties are used and add the option to ignore this error. This is a breaking change and will be introduced at a later time.
In the current documentation the JDB connects to the VM listening for a debugger on the the Virtual Schema's side. This is a so called "forward connection". While that concept is easier to understand for users, it has multiple drawbacks -- all revolving around the fact that for this to be useful you need to be able to see the listening port from the outside.
And that means the port needs to be forwarded from the UDF container and through any virtual networks that might be involved.
This is definitely too much trouble for a simple task like debugging.
A better solution is to use a reverse connection, because UDFs are designed so that they can connect to the outside world.
In com.exasol.adapter.jdbc.JdbcAdapter.tryAttachToOutputService(SchemaMetadataInfo)
an exception is thrown if the debug address cannot be parsed.
While that provides the user with a nice error message during creation of the VS, it also prevents dropping from working in case a wrong VS definition was used initially.
While it is highly unlikely that this happens, still dropping the schema must always work, because it is the "nuclear option" in case everything else fails.
DROP VIRTUAL SCHEMA
works -- regardless of any debug settingsProblem
postgres is incompatible to the SQL standard when it comes to identifiers:
The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard.
(https://www.postgresql.org/docs/current/sql-syntax-lexical.html)
EXASOL is SQL standard compliant: all unquoted identifiers are folded to upper case. Since the adapter is quoting all identifiers (since v1.1.1), there is an upper case / lower case mismatch that leads to an error.
Proposed solution
The postgres adapter dialect converts all identifiers to lower case. This way the virtual schema integration between exasol and postgres will work seamlessly if you don't use quoted upper case identifiers in postgres.
If you have tables with upper case characters in postgres, creating or refreshing the virtual schema will result in an error. You can still create the schema if you set a FORCE_CREATE property when creating the virtual schema.
Handling PostgreSQL identifiers is difficult because PostgreSQL is not SQL standard compliant (see #74). Currently the PostgreSQL adapter is not able to query identifiers that contain uppercase characters. You can create a virtual schema, but you won't be able to query these identifiers.
A workaround is not to use quoting at all (see https://github.com/exasol/virtual-schemas/blob/master/jdbc-adapter/doc/sql_dialects/postgresql.md#postgres-identifiers), however if this is not possible the adapter should support identifiers with uppercase characters.
For some queries, EXASOL requests any column. The current oracle dialect uses "TRUE" in the select list. However, Oracle does not support this.
Example:
SELECT 1 FROM VS.T T1 LEFT JOIN SYS.DUAL D1 ON 1=1;
Error:
Oracle tool failed with error code '904' and message 'ORA-00904: "TRUE": invalid identifier'.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.