exasol / database-migration Goto Github PK

https://my-bucket.s3-eu-central-1.amazonaws.com
caused an issue with nil values in Lua.
If we verfied the url before trying to run the script, we could have had a better error message, because the real url would be:
https://my-bucket.s3.eu-central-1.amazonaws.com
The 1st url has a - after s3, which is not valid.

I've had issues with numeric and decimal since max precision in sql server is 38 and in exasol 36, i've made quick fix by adding this case when PRECISION > 36 then 36 else PRECISION end.

Then i've added binary bwith this line

when 173 then '"' || column_name || '"' ||' ' ||'VARCHAR(2000000)' --binary

Using Exasol 6.2.1 and postgres 9.6.11 (AWS RDS), I can run the postgres_to_exasol.sql script after changing the create connection postgres_db line to match my environment, and the following text is displayed:

[2019-09-19 14:26:55] Run migrate_postgres_to_exasol.sql
[2019-09-19 14:26:55] Connecting to Exasol BYOL...
[2019-09-19 14:26:57] Using batch mode (1000 insert/update/delete statements max)
create schema if not exists database_migration
[2019-09-19 14:26:57] 0 row(s) affected in 113 ms
create or replace script database_migration.POSTGRES_TO_EXASOL(
CONNECTION_NAME              -- name of the database connection inside exasol -> e.g. postgres_db
,IDENTIFIER_CASE_INSENSITIVE -- true if identifiers should be stored case-inse...
[2019-09-19 14:26:57] 0 row(s) affected in 254 ms
create connection postgres_db to 'jdbc:postgresql://{my connection string here}
[2019-09-19 14:26:57] 0 row(s) affected in 133 ms
execute script database_migration.POSTGRES_TO_EXASOL(
'postgres_db', -- name of your database connection
true,          -- case sensitivity handling for identifiers -> false: handle them case sensitiv / true: handle them case insens...
[2019-09-19 14:27:02] completed in 4 s 430 ms
[2019-09-19 14:27:02] Summary: 4 of 4 statements executed in 6 s 305 ms (12011 symbols in file)

Here we see it completed in 4s, and when I look at my Exasol DB, I don't see any new tables imported from Postgres. Is there something else I should be doing to make this work? I've verified that the Postgres user in the connection string has access to information_schema.columns, so I believe the script can introspect properly.

I've tried to run the mysql migration script and i get an error saying schema LOAD_METADATA not found.

I suspect this might just be missing a single line.

Postgres has a column type called HSTORE which is represented as a CSV string with "key1"=>"value1", "key2"=>"value2" pairs. While this can be parsed with a Python UDF once in Exasol, it is inefficient compared to parsing JSON in Pyton since the cjson library is include with Exasol.

Postgres has a hstore_to_json() function which would convert an HSTORE column a JSON string. This Issue is a feature request to use that function so that when migrated to Exasol, HSTORE columns become JSON strings for more efficient parsing.

In the script there is a small error that creates table columns in the wrong order.

Currently script orders columns as follows (ordinal_position):

1, 10, 11, 12, 13, 2, 3, 4, 5, 6, 7, 8, 9
ist should be 1, 2, 3, 4, ... obviously.

Script can be easily amended with casting the column 'ordinal_position' to integer where order by ordinal_position is used.

I am running docker container of exasoldb from here. Another container running mysql is also on the same network but unable to create connection. I have double checked to make sure that mysql database container is accessible from exasol container.

Commands executed

CREATE OR REPLACE CONNECTION mysql_conn TO 'jdbc:mysql://172.19.0.2:3306/poc' USER 'root' IDENTIFIED BY 'Password101@';

select * from (import from JDBC at mysql_conn statement 'select ''Connection works'' from dual');

Error Message

Error: [ETL-5] JDBC-Client-Error: Connecting to 'jdbc:mysql://172.19.0.2:3306/poc' as user='root' failed: Could not create connection to database server. (Session: 1647722762398739007)

Hi,

what exactly is there to gain from using smaller VARCHAR fields, as the convert datatypes script suggests?
The Exasol documentation says "These strings are stored in their respective length.", so I would assume there is no space to be saved here?

If not that, then... what is the gain?

Best regards
Christian