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.

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.

svv_table_info
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.

sys.objects
sys.columns
sys.types
sys.indexes
sys.index_columns
sys.columns
sys.key_constraints
sys.tables
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.

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.

information_schema.columns
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.

sys.dba_tables
sys.dba_constraints
sys.dba_cons_columns
sys.all_users
all_ind_columns
all_indexes
all_cons_columns
all_constraints

To grant read only permission to catalog tables

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.

information_schema.tables
information_schema.columns
information_schema.table_constraints
information_schema.key_column_usage
information_schema.referential_constraints
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.

syscat.tables
syscat.columns
sysibmadm.admintabinfo
syscat.tabconst
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.