Oracle Business Intelligence provides DataDirect Connect ODBC drivers and driver managers for Linux and UNIX operating systems for connectivity to Microsoft SQL Server, Sybase ASE, Informix, Hive, and Impala databases.
After Oracle Business Intelligence is installed, the DataDirect Connect ODBC drivers are installed in ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib
.
You do not need to set the ODBCINI environment variable to set up the DataDirect Connect ODBC drivers. This variable is set automatically during installation.
Refer to System Requirements and Certification.
Note:
Amazon Redshift data sources are also supported. You need to use the Amazon Redshift ODBC driver available from Amazon Web Services. Configure the Amazon Redshift data source using the steps documented for other data sources.
When you install Oracle BI EE, the required DataDirect 7.1.5 drivers are installed and automatically configured.
You can define the default settings in obis.properties
and odbc.ini
files.
You need to modify your existing database configurations to use the DataDirect drivers. For information about modifying your existing database configuration, see the following procedures:
Configuring the DataDirect Connect ODBC Driver for Microsoft SQL Server Database
Configuring the DataDirect Connect ODBC Driver for MySQL Database
Configuring the DataDirect Connect ODBC Driver for Sybase ASE Database
Configuring the DataDirect Connect ODBC Driver for Informix Database
Configuring the DataDirect Connect ODBC Driver for Cloudera Impala Database
Configuring the DataDirect Connect ODBC Driver for Apache Hive Database
The name of the DataDirect ODBC driver file to connect to a Microsoft SQL Server database is ARsql27.so.
See System Requirements and Certification for supported versions of Microsoft SQL Server.
The name of the DataDirect ODBC driver file to connect to a MySQL database is ARmysql27.so.
The name of the DataDirect ODBC driver file to connect to a MySQL database is ARmysql27.so. See System Requirements and Certification.
The name of the DataDirect ODBC driver file to connect to a Sybase ASE database is ARase27.so.
See System Requirements and Certification for supported versions of Sybase ASE.
The name of the DataDirect ODBC driver file to connect to an Informix database is ARifcl27.so.
See System Requirements and Certification for supported versions of Informix.
The DataDirect ODBC driver file name, to connect to a Cloudera Impala database, is ARimpala27.so
.
See System Requirements and Certification for the supported versions of Cloudera Impala and Configuring Impala 1.3.x to Include a LIMIT Clause.
Impala 1.3.x requires that queries with an ORDER BY clause contain a LIMIT clause.
There are three methods to specify this clause in the configuration. Oracle recommends using the Modify the Impala daemon's default query options method. For the second and third methods, see Modifying the Impala DefaultOrderByLimit Alternate Methods.
Specifying a default order by limit using any of the following methods returns a maximum of 2,000,000 rows for queries with an ORDER by clause.
If you specify the LIMIT clause using the Modify the Impala daemon's default query options method, and your queries include an ORDER BY clause, then Impala returns a maximum of 2,000,000 rows. If this limit is exceeded, then Impala throws an exception.
For queries over 2,000,000 rows, specify a higher default_order_by_limit
value.
You can also specify the Default Order By Limit
by using the client instead of the Impala server.
Use this method if you do not have rights to modify the Impala daemon using the previous methods. If you use this method, then Impala silently truncates your value to 2,000,000 rows.
Use the first modifying the DefaultOrderByLimit option if your Impala environment is not managed by Cloudera Manager. Use the second DefaultOrderByLimit option if you do not have rights to modify the Impala daemon.
Modify the Impala Daemon's Default Query Options Without Cloudera Manager
If your environment is managed by Cloudera Manager and you have the required permissions, you should use the recommended method for updating the DefaultOrderByLimit. See Configuring Implaa 1.3x to Include a Limit Clause.
If your Impala environment is not managed by Cloudera Manager, use the Impala product documentation to help you modify the LIMIT clause. To complete this task, see “Configuring Impala Startup Options Through the Command Line”, located in the CDH 5 Installation Guide.
If you specify the LIMIT clause using this method and your queries include an ORDER BY clause, then Impala returns a maximum of 2,000,000 rows. If this limit is exceeded, then Impala throws an exception.
After using the instructions in “Configuring Impala Startup Options Through the Command Line,” add the following entry in IMPALA_SERVER_ARGS
:
-default_query_options 'default_order_by_limit=2000000;abort_on_default_limit_exceeded=true'
Modify the DefaultOrderByLimit Parameter in the odbc.ini Impala DSN Entry
Use this method if you do not have rights to modify the Impala daemon using the previous methods. If you use this method, then Impala silently truncates your value to 2,000,000 rows.
If you need your query to return more than 2,000,000 rows, then specify a higher DefaultOrderByLimit
parameter value.
You can specify the Default Order By Limit using the client instead of the Impala server.
odbc.ini
file from the BI_DOMAIN/config/fmwconfig/bienv/core
directory.DefaultOrderByLimit
parameter.DefaultOrderByLimit=2000000
.odbc.ini
file.