Databases/Warehouses

Some of the databases and warehouse available when clicking on Add New Connection might require more information than the one shown in the previous section, or could have extra options.

Redshift

To add a Redshift connection, you will have to provide:

• Connection name, which you will assign to this connection for internal use.

• A JDBC URL. A sample JDBC URL is shown under this text.

jdbc:redshift://redshift-cluster-1.XXXXXXXXXX.us-east-1.redshift.amazonaws.com:5439/dev?ssl=false

• Credentials, which will be a username and a password that will provide access to this database.

Optionally, you may provide s3TempDir and s3AwsIamRole for better performance, as explained in the following link.

Redshift IAM role for Copy Unload to S3AWS CLOUD DBA

s3TempDir

s3n://<S3_Bucket>/

s3AwsIamRole

arn:aws:iam::XYZ:role/Redshift_Role

It is recommended to create a read only user account in Redshift. The user id needs to have access to the following system tables to read the metadata of all the tables.

1. svv_table_info

2. information_schema.views

SQL Server

To add an SQL Server connection, you will have to provide:

• Connection name, which you will assign to this connection for internal use.

• A JDBC URL. A sample JDBC URL is shown under this text.

jdbc:sqlserver://XXXXXX:1433;databaseName=XXXXXXX;

• Credentials, which will be a username and a password that will provide access to this database.

It is recommended to create a read only user in Sql Server. The read only user id should have read only access on the databases/schemas where data resides and also have access to the following system tables to read the metadata of all the tables.

1. sys.objects

2. sys.columns

3. sys.types

4. sys.indexes

5. sys.index_columns

6. sys.columns

7. sys.key_constraints

8. sys.tables

9. sys.all_columns

Postgres

To add a PostgresSQL connection, you will have to provide:

• Connection name, which you will assign to this connection for internal use.

• A JDBC URL. A sample JDBC URL is shown under this text.

jdbc:postgresql://<jdbcHost>:XXXX/<jdbcDatabaseName>

• Credentials, which will be a username and a password that will provide access to this database.

If SSL is enabled, additional parameters will be required for the JDBC URL as shown under this text.

jdbc:postgresql://<jdbcHost>:XXXX/<jdbcDatabaseName>&ssl=true**&sslmode=require&sslfactory=org.postgresql.ssl.NonValidatingFactory**

Create a new read only user for Postgres Testing

The user id should only have read permission on the data. Also, Vexdata needs permission to read metadata. The user id should have read only permissions on the following system tables

pg_class
information_schema.table_constraints
information_schema.columns
pg_namespace
pg_catalog

Snowflake

To add a Snowflake connection, you will have to provide:

• Connection name, which you will assign to this connection for internal use.

• A JDBC URL. A sample JDBC URL is shown under this text.

jdbc❄️//XXX72530.us-east-1.snowflakecomputing.com/?db=<jdbcDatabaseName>&schema=<schemaName>

• Credentials, which will be a username and a password that will provide access to this database.

It is recommended to create a read only user account in Snowflake. The user id needs to have access to the following system tables to read the metadata of all the tables.

1. information_schema.columns

2. information_schema.tables

Oracle

It is recommended to create a read only user account in Oracle. The user id needs to have access to the following system tables to read the metadata of all the tables.

1. sys.dba_tables

2. sys.dba_constraints

3. sys.dba_cons_columns

4. sys.all_users

5. all_ind_columns

6. all_indexes

7. all_cons_columns

8. all_constraints

To grant read only permission to catalog tables

grant SELECT_CATALOG_ROLE to <user>;

Maria​

It is recommended to create a read only user account in Maria. The user id needs to have access to the following system tables to read the metadata of all the tables.

1. information_schema.tables

2. information_schema.columns

3. information_schema.table_constraints

4. information_schema.key_column_usage

5. information_schema.referential_constraints

6. information_schema.statistics

DB2

It is recommended to create a read only user account in DB2. The user id needs to have access to the following system tables to read the metadata of all the tables.

1. syscat.tables

2. syscat.columns

3. sysibmadm.admintabinfo

4. syscat.tabconst

5. syscat.keycoluse

Bigquery

It is recommended to create a read only user account in Maria. The user id needs to have access to the following system tables to read the metadata of all the tables.

1. INFORMATION_SCHEMA.SCHEMATA

2. INFORMATION_SCHEMA.TABLES

3. INFORMATION_SCHEMA.COLUMNS