CREATE CONNECTION
Purpose
Use this statement to create a connection.
Prerequisites
- You need to have the system privilege
CREATE CONNECTION
. - If the
OR REPLACE
option has been specified and the connection already exists, the rights for DROP CONNECTION are also needed.
Syntax
create_connection::=
Usage notes
-
You can use external connections within the IMPORT and EXPORT statements. Users must have the corresponding access rights to the connection (through GRANT). The connection, including the
ADMIN OPTION
, is automatically granted to the creator. -
Connections can control the read access for users who want to use scripts for processing data from local buckets stored in BucketFS.
-
You can define an Exasol connection (EXA to EXA), a native connection to an Oracle database, a JDBC connection to any database, or a connection to a file server. The JDBC drivers can be addressed within the connection string (for example, jdbc:mysql, jdbc:postgres).
-
The declaration of username and password is optional and can be specified within the IMPORT and EXPORT statements.
-
Invalid connection data will not be noticed before the usage within the IMPORT and EXPORT statements.
-
Information about database connections can be found in the corresponding system tables, for example EXA_ALL_CONNECTIONS.
-
You can rename a connection by using the RENAME command.
-
Connection names follow the same naming rules as SQL identifiers, but connection names are not case sensitive. This means that the connection names
Test
,TEST
, andtest
are synonymous. For more information, refer to SQL identifier.
Parallel EXA to EXA connections
To enable parallelization in IMPORT/EXPORT operations using an Exasol connection (EXA), you can define multiple hosts in the connection string. Parallel connections are then established using the existing database port. No additional ports need to be opened by the database.
The connection string supports OpenID authentication and the following ODBC parameters: SSLCertificate
, AuthMethod
, HostTimeOut
, LoginTimeout
, Encryption
, LogMode
, EXALogFile
, and Fingerprint
. Other parameters are not supported.
You must include all hosts in the connection string.
Examples
CREATE CONNECTION exa_connection
TO '192.168.6.11,192.168.6.12:8563;HostTimeOut=1000;Encryption=Y'
USER 'my_user'
IDENTIFIED BY 'my_secret';
CREATE CONNECTION ora_connection TO '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.54)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)))';
CREATE CONNECTION jdbc_connection_1
TO 'jdbc:mysql://192.168.6.1/my_db';
CREATE CONNECTION jdbc_connection_2
TO 'jdbc:postgresql://192.168.6.2:5432/my_db?stringtype=unspecified';
CREATE CONNECTION ftp_connection
TO 'ftp://192.168.1.1/'
USER 'my_user'
IDENTIFIED BY 'my_secret'
PUBLIC KEY 'my_public_key';
CREATE CONNECTION S3_MY_BUCKETNAME
TO 'http://<my_bucketname>.s3.<my_region>.amazonaws.com'
USER '<my_access_key>'
IDENTIFIED BY '<my_secret_key>';
A fully qualified S3 URL in the format <bucket-name>.s3.<region-code>.amazonaws.com
will become available immediately when you have created the bucket. A URL in the legacy global endpoint format <bucket-name>.s3.amazonaws.com
may need up to 24 hours to become available.