Vertica Connection¶
The Vertica connection type provides connection to a Vertica database.
Configuring the Connection¶
- Host (required)
The host to connect to.
- Schema (optional)
Specify the schema name to be used in the database.
- Login (required)
Specify the user name to connect.
- Password (required)
Specify the password to connect.
- Extra (optional)
Specify the extra parameters (as json dictionary) that can be used in Vertica connection.
The following extras are supported:
backup_server_node: See Connection Failover.binary_transfer: See Data Transfer Format.connection_load_balance: See Connection Load Balancing.connection_timeout: The number of seconds (can be a nonnegative floating point number) the client waits for a socket operation (Establishing a TCP connection or read/write operation).disable_copy_local: See COPY FROM LOCAL.kerberos_host_name: See Kerberos Authentication.kerberos_service_name: See Kerberos Authentication.log_level: Enable vertica client logging. Traces will be visible in tasks log. See Logging.request_complex_types:: See SQL Data conversion to Python objects.session_label: Sets a label for the connection on the server.ssl: Support only True or False. See TLS/SSL.unicode_error: See UTF-8 encoding issues.use_prepared_statements: See Passing parameters to SQL queries.workload: Sets the workload name associated with this session.
See vertica-python docs for details.
Example “extras” field:
{ "connection_load_balance": true, "log_level": "error", "ssl": true }
or
{ "session_label": "airflow-session", "connection_timeout": 30, "backup_server_node": ["bck_server_1", "bck_server_2"] }
Using SQLAlchemy¶
VerticaHook.get_sqlalchemy_engine() and the features built on it (for example
DataFrame.to_sql with the hook’s engine, inspector or get_table_schema) need the
sqlalchemy extra and a Vertica SQLAlchemy dialect. The hook builds a
vertica+vertica_python:// URL from the connection, which is the name Vertica dialects register.
With SQLAlchemy 2.x, sqlalchemy-vertica has been checked to work (connection, inspection and
DataFrame.to_sql).The official vertica-sqlalchemy-dialect requires SQLAlchemy 1.4.44 or lower, so it does not work with SQLAlchemy 2.x.
pip install 'apache-airflow-providers-vertica[sqlalchemy]' sqlalchemy-vertica
Connection extras are passed to the dialect as URL query parameters, and the dialect decides how to convert them for vertica-python.