Loadstaff47

ODBC driver managers use configuration files to define and configure ODBC datasources and drivers. To configure an ODBC connection for Mac OS X, note the followingprocedure:

• Odbc Dsn Oracle
• Odbc Driver For Oracle

Step 1: Driver Installer Updates Sample Configuration Files

I have similar issue, i have not defined DSN while on boarding account but the account is failing with 'IM002 Native error: 0 Message: MicrosoftODBC Driver Manager Data source name not found and no default driver specified'. I did check for oracle driver in CPM and it is installed. You have to add the Database tns entries in the tnsnames.ora file in the same linux serer with name ASPD that you have used for your ODBC dsn name above. From the ODBC Administrator dialog, choose either the User DSN or System DSN tab and click Add. Select the Connector/ODBC driver and click OK. You will be presented with the Data Source Name (DSN) dialog. Enter the Data Source Name and an optional Description for the DSN. Oracle doesn't provide a Mac ODBC driver. If Microsoft doesn't ship an ODBC driver for Oracle on the Mac, you'll have to look for third-party tools. I'd start with Merant, since they produce a lot of ODBC drivers. Justin Cave ODBC Development.

Before you connect to Drill through an ODBC client tool on Mac OS X, the driver installer copies the following configuration files in /Library/mapr/drill/Setup to your home directory unless the files already exist in your home directory:

• mapr.drillodbc.ini
• odbc.ini
• odbcinst.ini

In your home directory, rename the files as hidden files. Use sudo if necessary:

• .mapr.drillodbc.ini
• .odbc.ini
• .odbcinst.ini

The installer for Mac OS X creates a sample user DSN in odbc.ini in either of the following locations:

• ~/Library/ODBC/odbc.ini
• ~/.odbc.ini

Depending on the driver manager you use, the user DSN in one of these files will be effective.

Note

The System and User DSN use different ini files in different locations on OS X.

Step 2: Set Environment Variables

The driver installer installs the .mapr.drillodbc.ini file to your home directory and adds an entry to the $HOME/.odbc.ini file.

If you installed the iODBC driver manager using the DMG, the dynamic linker (DYLD) libraries are installed in /usr/local/iODBC/lib. The launchd.conf file contains contents such as the following:

Restart the Mac OS X or run launchctl load /etc/launchd.conf.

Step 3: Define the ODBC Data Sources in .odbc.ini

Define the ODBC data sources in the ~/.odbc.ini configuration file for your environment.

You set the following properties for using Drill in embedded mode:

You set the following properties for using Drill in distributed mode:

Authentication Properties

If the Drillbit requires authentication, uncomment the AuthenticationType, add an AuthenticationType, and configure properties. If the Drillbit does not require authentication (or to configure no password protection), you can use the No Authentication option. You do not need to configure additional settings.

• Kerberos
  • See the MIT Kerberos documentation for installing and configuring a Kerberos environment, which is beyond the scope of the information provided here.
  • To specify the Kerberos mechanism:
    • Set the AuthenticationType to Kerberos.
    • Set the KrbServiceHost property to the FQDN of the Drill server host.
    • Set the KrbServiceName property to the Kerberos service principal name of the Drill server.
• Plain (or Basic Authentication)
  • Configure the UID to an appropriate name for accessing the Drill server.
  • Set the PWD property to the password corresponding to the UID.

To configure no password protection, select No Authentication.

Direct and ZooKeeper Quorum Properties

To use Drill in distributed mode, set ConnectionType to Zookeeper, get the ZKQuorum and ZKClusterID values from the drill-override.conf file, and define the ZKQuorum and ZKClusterID properties. Format ZKQuorum as a comma separated list of ZooKeeper nodes in the following format:
<host name/ip address> : <port number>, <host name/ip address> : <port number>, . . .

For example:

• ZKQuorum=centos23:5181,centos28:5181,centos29:5181
• ZKClusterID=docs41cluster-drillbits

To use Drill in local mode, do not define the ZKQuorum and ZKClusterID properties. Start Drill using the drill-localhost command, set ConnectionType to Direct, and define HOST and PORT properties. For example:

• HOST=<IP address of drillbit>:5181
• PORT=31010

Driver Configuration Options describes configuration options available for controlling thebehavior of DSNs using the Drill ODBC Driver.

Sample Configuration File

The following sample shows a possible configuration for using Drill in embedded mode.

Note

The password should be provided in the connection string or ODBC application at connection time, not in the INI file.

Step 4: Configure the Drill ODBC Driver

Configure the Drill ODBC Driver for your environment by modifying the .mapr.drillodbc.ini configurationfile. This configures the driver to work with your ODBC driver manager. The following sample shows a possible configuration, which you can use as is if you installed the default iODBC driver manager.

Example

Configuring .mapr.drillodbc.ini

To configure the Drill ODBC Driver in the .mapr.drillodbc.ini configuration file, complete the following steps:

1. Open the .mapr.drillodbc.ini configuration file in a text editor.

2. Edit the DisableAsync setting if you want to enable a synchronous ODBC connection for performance reasons. Change the default 0 to 1 to disable the asynchronous and enable the synchronous connection. A change in state occurs during driver initialization and is propagated to all driver DSNs.

1. Save the .mapr.drillodbc.ini configuration file.

Next Step

Refer to Testing the ODBC Connection.

Similar to Windows, macOS utilizes named data sources (DSNs) for connecting ODBC-based client applications to Snowflake.

In this Topic:

Prerequisites¶

Operating System¶

For a list of the operating systems supported by Snowflake clients, see Operating System Support.

iODBC¶

The Snowflake ODBC driver for Mac requires iODBC, which is available for download from:

To install iODBC:

1. After downloading iODBC, double-click on the downloaded .dmg file.

2. Double-click on the installer file, iODBC-SDK.pkg, and follow the prompts.

Note

iODBC provides a GUI administrator tool for configuring drivers and DSNs; however, this tool has not been tested for use with Snowflake and, therefore, should not be used to create or manage DSNs. Use ODBC Manager instead.

ODBC Manager — Optional/Recommended¶

ODBC Manager is a GUI tool for configuring drivers and creating/managing DSNs. The tool is optional because you can also create DSNs manually by editing the appropriate odbc.ini file. ODBC Manager is available from:

To install ODBC Manager:

1. After downloading ODBC Manager, double-click on the downloaded .dmg file.

2. Double-click on the installer file, ODBCManager.pkg, and follow the prompts.

The installer installs ODBC Manager in the ~/Applications/Utilities directory.

Step 1: Install the ODBC Driver¶

To install the Snowflake ODBC driver for macOS:

1. If you haven’t already downloaded the driver, download it now. For details, see Downloading the ODBC Driver.

2. Double-click on the downloaded .dmg file, snowflake_odbc_mac-<version>.dmg.

3. Double-click on the installer file, snowflakeODBC_<version>.pkg, and follow the prompts.

   You will likely be prompted for the administrator/sudo password for the machine on which you are installing the driver.

If you choose the default directory when prompted, the installer installs the ODBC driver files in the following directories:

Step 2: Configure the ODBC Driver¶

To configure the ODBC driver for macOS, create one or more data source (DSNs), which are stored in the following files, depending on the type of DSN you create:

• User DSNs: ~/Library/ODBC/odbc.ini

• System DSNs: /Library/ODBC/odbc.ini

To create a DSN, either use ODBC Manager or edit the appropriate odbc.ini file.

Creating a DSN Using ODBC Manager¶

To create a DSN in ODBC Manager:

1. Start ODBC Manager.

2. Click on User DSN or System DSN.

3. Click the Add button.

4. Select the driver to use (Snowflake) and click OK.

5. Enter the name of the DSN and optionally a description.

6. Create a Keyword/Value pair for each DSN parameter:

   1. Click Add to create a Keyword/Value pair.

   2. Select Keyword and replace it with the parameter name.

   3. Select Value and replace it with the value for the parameter.

   Repeat this process for each parameter. For details about the parameters that can be set for each DSN, see ODBC Configuration and Connection Parameters.

7. Click OK to create the DSN.

If you are creating the first user or system DSN for the ODBC driver, ODBC Manager creates the odbc.ini file in the corresponding directory for the type of DSN you are creating. If you are creating additional DSNs, ODBC Manageradds entries for each DSN to the existing odbc.ini file.

Creating a DSN by Adding an Entry in the odbc.ini File¶

If a user or system DSN has already been created for the driver, add the new entry to the odbc.ini file that already exists in the corresponding directory for the type of DSN you are creating. If you are creating the first DSNfor the driver, you must manually create the odbc.ini file and add the entry to the file.

For each DSN, specify:

• DSN name and driver name (Snowflake), in the form of <dsn_name>=<driver_name>.

• Directory path and name of the driver file, in the form of Driver=/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib.

• Connection parameters, such as server and uid (user login name). Any connection parameters you add to the DSN do not need to be specified in the ODBC connect string.

• Any additional parameters, such as default role, database, and warehouse.

Odbc Dsn Oracle

Parameters are specified in the form of <parameter_name>=<value>. For details about the parameters that can be set for each DSN, see ODBC Configuration and Connection Parameters.

The following example illustrates an odbc.ini file containing two drivers, testodbc1 for account xy12345 (located in the AWS US West region) and testodbc2 for account yz23456(located in the AWS US East region):

Note the following:

• Both testodbc1 and testodbc2 have default roles.

• testodbc2 also has a default database and warehouse.

Step 3: Test the ODBC Driver¶

You can use the iodbctest command line utility provided with iODBC to test the DSNs you create.

When prompted for the ODBC connect string, enter the required connection parameters (DSN name, server, user login name, and password), as well as any other parameters that you would like to enter as part of the connect string. Theconnect string takes parameters in the form of <parameter_name>=<value>, e.g. dsn=testodbc2, with each parameter separated by a semi-colon (;) and no blank spaces. For the list of supported parameters, seeODBC Configuration and Connection Parameters.

Note

If you set the server and user login name in the DSN, the only required parameters in the connect string are the DSN name and user password.

Odbc Driver For Oracle

For example: