These commands link Cirro to the target database. Each distinct Database type should be added with its own CREATE SYSTEM command.

Syntax

CREATE SYSTEM name OF TYPE type USING HOST "HostIp"
OPTIONS (
(USER 'dbusername' PASSWORD 'dbpassword' )
[datasource_clauses | optional_clauses]
(DRIVER 'drivername')
);

CONNECTION_STRING ‘jdbc:oracle:thin:@tokyoora_medium?TNS_ADMIN=/opt/cirro/var/certs’

Required Clauses

  • name - the user-specified name for the datasource.

  • datasource - supported datasource.

  • HostIp: The network-accessible IP address or server name for the datasource.

  • USER and PASSWORD - datasource user account name and password.

  • driver - driver created with CREATE DRIVER

  • DEFAULTDATABASE - default database Cirro will connect to.

  • TEMPDATABASE - temporary database for use by Cirro.

Datasource Specific Clauses

Datasource Specific clauses are required or optional depending on the database.

  • All databases (required on Vertca)

    • TEMPSCHEMA - Schema used with TEMPDATABASE.
  • Hadoop

    • OUTPUTDIR - The location for the Cirro output files on HDFS.

    • HDFSPORT - The HDFS listening port.

    • HDFSURL - The fully-qualified HDFS location.

  • HIVE

    • SQOOP - Identifies the Sqoop service connected to this hive process.

    • HADOOP - Identifies the hadoop service connected to this hive process.

    • WAREHOUSEDIR - The hive warehouse location.

    • HIVEVERSION - The hive version. Cirro will provide the exact value to use.

    • SERDECLASSNAME - The hive SerDe class name. Cirro will provide the exact value to use.

    • HIVEOUTPUTCLASSNAME - The hive output class name. Cirro will provide the exact value to use.

Optional Clauses

Optional clauses are available with all Datasource types.

  • OBJECTCASESENSITIVE - Informs Cirro whether the data source is case sensitive. Options are “false” (default) or “true”.

  • READONLY - Marks the system as read only. Statements that modify structures or data, or enable modifying structures or data are blocked with an error.

  • CONNECTION_STRING - Allows the user to completely override the JDBC connection string for certain special types of connections. For example:

'jdbc:oracle:thin:@tokyoora_medium?TNS_ADMIN=/opt/cirro/var/certs'
  • CONNECTION_STRING_PROPERTIES - A string appended to the connection string URL used to make JDBC connections to the underlying RDBMS. Optional on HIVE.

  • CONNECTION_PROPERTIES - A list of connection properties delimited by a semi-colon. Optional on HIVE.

  • SAME_AUTHORIZATION (TRUE FALSE) - Force connections from Cirro Data Hub to connect to Remote system using LDAP user.
  • CONNECTION_PREFIX - Optional LDAP setting. May not be required for all system types.

Additional Information

  • CONNECTION_STRING_PROPERTIES are propagated to the Sqoop interface and override any CONNECTION_PROPERTIES.

  • CONNECTION_PROPERTIES are passed as the java Properties object into the JDBC connection made to the underlying RDBMS system. They are not propagated to JDBC connections made via Sqoop.

Examples

Aurora

CREATE SYSTEM aurora OF TYPE aurora USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword';

BigSQL

CREATE SYSTEM bigsqlsystem OF TYPE bigsql USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', DEFAULTDATABASE 'bigsqldefault', DEFAULTSCHEMA 'bigsqlschema', DRIVER 'bigsqldriver');

Cassandra

CREATE SYSTEM cassandrasystem OF TYPE cassandra USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword';

Greenplum

CREATE SYSTEM greenplumsystem OF TYPE greenplum USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', DEFAULTDATABASE 'greenplumdefault', TEMPDATABASE 'greenplumtemp', DRIVER 'greenplumdriver');

LDAP/Active Directory

CREATE SYSTEM SQLSERVER1 OF TYPE SQLSERVER USING HOST "198.51.100.4" OPTIONS ( USER 'cirroclient', PASSWORD 'password'), TEMPDATABASE 'temppoc', TEMPSCHEMA 'dbo', DRIVER 'sqlserver_drivername', SAME_AUTHORIZATION 'true', CONNECTION_STRING_PROPERTIES ';domain=ENGINEERING');

MySQL

  • Database Name and Schema are the same.

  • May require CONNECTION_PROPERTIES ‘allowPublicKeyRetrieval=true;useSSL=false’

CREATE SYSTEM mysqlsystem OF TYPE mysql USING HOST "198.51.100.2" OPTIONS (USER 'dbuser', PASSWORD 'dbpassword', TEMPDATABASE 'mysqltemp', CONNECTION_PROPERTIES 'allowPublicKeyRetrieval=true;useSSL=false', DRIVER 'mysqldriver');

Netezza

CREATE SYSTEM netezzasystem OF TYPE netezza USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', DEFAULTDATABASE 'netezzadefault', TEMPDATABASE 'netezzatemp', TEMPSCHEMA 'netezzaschema', DRIVER 'netezzadriver');

Oracle - Pre Oracle 12: Database name is same as Oracle SID.

CREATE SYSTEM oraclesystem OF TYPE oracle USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', TEMPDATABASE 'oracletemp', DEFAULTDATABASE 'oracledefault', DRIVER 'oracledriver', READONLY true) ;

PostgreSQL

CREATE SYSTEM postgressystem OF TYPE postgresql USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', DEFAULTDATABASE 'postgresdefault', TEMPDATABASE 'postgrestemp', DRIVER 'postgresdriver');

RDBMS RMS

CREATE SYSTEM rdbms2 OF TYPE rdbmsrms USING HOST "198.51.100.7"; CREATE SYSTEM rdbms2 OF TYPE rdbmsrms USING HOST "198.51.100.7", "198.51.100.8", "198.51.100.9";

Redshift

CREATE SYSTEM redshiftsystem OF TYPE redshift USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', DEFAULTDATABASE 'redshiftdefault', TEMPDATABASE 'redshifttemp', TEMPSCHEMA 'redshiftschema', DRIVER 'redshiftdriver');

SQL Server

CREATE SYSTEM sqlserversystem OF TYPE sqlserver USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', TEMPDATABASE 'sqlservertemp', TEMPSCHEMA 'sqlserverschema', DRIVER 'sqlserverdriver');

Sybase IQ

CREATE SYSTEM sybaseiqsystem OF TYPE SYBASEIQ USING HOST "198.51.100.4" OPTIONS (USER 'dbuser', PASSWORD 'dbpassword', TEMPDATABASE 'sybaseiqtemp', DEFAULTDATABASE 'sybaseiqdefault', TEMPSCHEMA 'sybaseiqschema', DRIVER 'sybaseiqdriver') ;

Teradata

CREATE SYSTEM teradatasystem OF TYPE Teradata USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', TEMPDATABASE 'teradatatemp', DEFAULTDATABASE 'teradatadefault', DRIVER ' teradatadriver');

Vertica

CREATE SYSTEM verticasysem OF TYPE vertica USING HOST "198.51.100.2" OPTIONS ( USER 'dbuser', PASSWORD 'dbpassword', DEFAULTDATABASE 'verticadefault', TEMPDATABASE 'verticatemp', TEMPSCHEMA 'verticaschema', DRIVER 'verticadriver');