JDBC connection string for Netezza

0 votes
Hi. I am using Netezza as my database for sqoop. What connection string should I use to make connections between sqoop and Netezza database? Thanks
Jun 6, 2019 in Big Data Hadoop by Raman
213 views

3 answers to this question.

0 votes

Hi. The syntax of the connection string format to be used for Netezza is as follows:

jdbc:netezza://<HOST>:<PORT>/<DATABASE_NAME>

Refer to the below example:

jdbc:netezza://my_netezza_server_hostname:5480/my_database_name
answered Jun 6, 2019 by jai
0 votes

HI..

I HOPE BELOW INFORMATION MAY HELP YOU-

To connect to a JDBC data source, you must create a connection string.

A Netezza JDBC connection string consists of the following parts in the following order:

  • jdbc:netezza:// (mandatory).
  • A host name (mandatory) and a port number (optional if you are using port 5480), followed by a forward slash (/). If you specify a port number, separate it from the host name with a colon (:).
  • A database name followed by a semicolon (;) (mandatory).
  • Property names and their values, specified as name=value pairs (optional). Separate a name=value pair from the next name=value pair by using a semicolon (;).

An example follows. In the example, main is the host name, and sales is the database name.

jdbc:netezza://main:5490/sales;user=admin;password=password;loglevel=2;logdirpath=C:\
Copier le code

The following table contains the properties and values that you can specify when creating the connection string. The properties and values are not case sensitive.

Table 1. Connection string properties and values
Property Description
applicationName Specifies the application name.
autocommit Specifies whether autocommit applies to the connection. Valid values are true (the default) and false.
batchSize Specifies the number of rows to be fetched in a cycle. The default is 256.
caCertFile Specifies the name of the certificate authority (CA) file that is used to authenticate connections. If you use a CA for authentication, the name of the CA file must match the name that the security administrator defined for the database to which you are attempting to connect. For more information about security and authentication, see the IBM® Netezza System Administrator’s Guide.
clientHostName Specifies the client host name.
clientUser Specifies the client user.
database Specifies the database name.
DatabaseMetaData Specifies whether you can get metadata information that is not restricted to the current catalog and schema. Valid values are false (the default, meaning that the information is restricted to the current catalog and schema) and true.
description Specifies the description for the data source.
dsn Specifies a data source name. If you specify this property, the connection string properties and values in the nzjdbc.ini file that are associated with that data source are used, unless you specify those properties and values directly in the connection string. For example, if you specify the connection string jdbc:netezza//localhost/dsn=nzsql, the properties and values that are associated with the nzsql data source name in the nzjdbc.ini file are used, unless you specify the properties and values directly in the connection string.

You can create the nzjdbc.ini file by using a GUI on Windows operating systems or command-line prompts on Linux and UNIX operating systems. For information about creating the nzjdbc.ini file, see Configuring the JDBC data source by using an nzjdbc.ini file (Windows) or Configuring the JDBC data source by using an nzjdbc.ini file (UNIX and Linux).

host Specifies the name of the host to connect to.
ignoreUpdateCount Specifies whether the JDBC driver is allowed to export more than two billion rows (the maximum int value). Valid values are off (the default) and on.
krbLoginModule Specifies the entry module name of the set of Kerberos properties from the login.conf file. The JDBC driver uses the login module class to create authentication tickets.
krbSSODelegation Specifies that if you configure an IBM Cognos Business Intelligence application in Kerberos SSO mode, the JDBC driver uses the authentication ticket that the Cognos product provides.
logDirPath Specifies the log directory into which the log files are written.
loggerType Specifies the type to be used for logging. Valid values are TEXT (the default) and HTML.
loginTimeout Specifies the maximum wait time to make a connection. The default is infinite.
logLevel Specifies the log level for the connection. The default is OFF.
port Specifies the port number.
readonly Specifies whether the connection is read only. Valid values are false (the default) and true.
securityLevel Specifies the security level. Values are as follows:

onlySecured

Specifies that only connections that use SSL to encrypt data are accepted. This security level increases security but reduces performance somewhat, because of the necessity of encrypting the data stream.

onlyUnsecured

Specifies that only connections that do not use SSL to encrypt data are accepted. This security level can help increase performance but carries the risks that come with an unencrypted connection.

preferredSecured

Specifies that connections that use SSL are preferred but that a connection is not refused solely because it does not use SSL encryption. This value is the default.

preferredUnsecured

Specifies that connections that do not use SSL are preferred but that a connection is not refused solely because it uses SSL encryption.

schema Specifies the name of the schema within the specified database on the Netezza® system. This property is used for Netezza systems that support multiple schemas within a database. If you do not specify a schema, you connect to the default schema of the database.

ImportantCalling the setSchema method to specify a new schema causes all future queries, including those using previously created or prepared Statement objects, to use the new schema. This behavior is different from that documented in the Oracle JDBC specification.

user Specifies the user name to connect to the database.
password Specifies the password for authentication.
answered Jul 15 by anonymous
0 votes

HI..

Netezza is one of the widely used MPP databases. You connect to it by using various methods and programming languages. Netezza supports ODBC, OLEDB, and JDBC drivers for connections. Connection to Netezza using a JDBC driver is easy and one of the widely used methods. In this article, we will check how to connect Netezza using JDBC driver and some working examples.

How to Connect Netezza using JDBC Driver and working Examples

Netezza JDBC Driver

Netezza provides a JDBC driver, you can use that driver from any programming language that supports JDBC connections such as Java, Python, etc. You can download the JDBC driver from IBM to fix the central site. You should have a JDBC jar by the name nzjdbc.jar.

Alternatively, you will get software packages if you purchase Netezza or IBM PureData Systems for analytics.

Before going deep into using Netezza JDBC driver, you need to install jaydebeapi module into python. Though you can use any module that supports JDBC drivers, jaydebeapi is one of the easy module that I have been using.

Install Jaydebeapi

The JayDeBeApi module allows you to connect from Python code to databases using Java JDBC. It provides a Python DB-API v2.0 to that database.

You can install it using pip:

pip install Jaydebeapi

Set CLASSPATH to Driver Location

As we have no other dependent jar for this Netezza JDBC driver, you can directly refer to this driver in your jaydebeapi module. Alternatively, you can export jar location to the CLASSPATH shell variable and run your python program without needing to set jar location in your module.

How to Connect Netezza using JDBC Driver?

In this section, we will discuss how can we connect Netezza using JDBC driver. I will be using python and jaydebeapi to execute the Netezza JDBC driver.

Once you have Netezza jar in place and installed the required modules, you are ready to access Netezza from within your Python program using a JDBC driver.

Note that, Netezza JDBC driver class name is “org.netezza.Driver

Here is the code that can help you:

import jaydebeapi, os

dsn_database = "TESTDB"
dsn_hostname = "192.168.100.210"
dsn_port = "5480"
dsn_uid = "admin"
dsn_pwd = "password"
jdbc_driver_name = "org.netezza.Driver"
jdbc_driver_loc = os.path.join('D:\\Work\\Connections_Softwares\\Jar\\nzjdbc.jar')

sql_str = "select now()"

connection_string='jdbc:netezza://'+dsn_hostname+':'+dsn_port+'/'+dsn_database

url = '{0}:user={1};password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("Connection String: " + connection_string)

conn = jaydebeapi.connect(jdbc_driver_name, connection_string, {'user': dsn_uid, 'password': dsn_pwd},
jars=jdbc_driver_loc)

curs = conn.cursor()
curs.execute(sql_str)
result = curs.fetchall()

print(result[0])

Here is the sample output:

URL: jdbc:netezza://192.168.100.210:5480/TESTDB:user=admin;password=password
Connection String: jdbc:netezza://192.168.100.210:5480/TESTDB
('2018-12-02 06:08:32',)
answered 2 minutes ago by SRI

Related Questions In Big Data Hadoop

0 votes
1 answer

Connection String for MySQL database using Sqoop

The format for the connection string and ...READ MORE

answered Jun 6, 2019 in Big Data Hadoop by John
474 views
0 votes
1 answer

JDBC connection to Hive using TLS/SSL

Hi. The properties are right but in ...READ MORE

answered May 31, 2019 in Big Data Hadoop by Karan
265 views
0 votes
1 answer

Configuring JDBC Clients for LDAP Authentication with HiveServer2

JDBC client requires a connection URL as ...READ MORE

answered Jun 3, 2019 in Big Data Hadoop by Raman
549 views
0 votes
1 answer

How to set alternate user for new connection in Hue?

You can use the hive.server2.proxy.user property to do ...READ MORE

answered Jun 3, 2019 in Big Data Hadoop by Lohith
81 views
+1 vote
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 10,950 points
6,140 views
0 votes
1 answer

hadoop.mapred vs hadoop.mapreduce?

org.apache.hadoop.mapred is the Old API  org.apache.hadoop.mapreduce is the ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 10,950 points
938 views
+1 vote
11 answers

hadoop fs -put command?

put syntax: put <localSrc> <dest> copy syntax: copyF ...READ MORE

answered Dec 7, 2018 in Big Data Hadoop by Aditya
39,829 views
–1 vote
1 answer

Hadoop dfs -ls command?

In your case there is no difference ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by kurt_cobain
• 9,320 points
2,305 views
0 votes
1 answer

What is the PostgreSQL connection string for Sqoop?

The format of the connection string is: jdbc:postgresql://<HOST>:<PORT>/<DATABASE_NAME> You ...READ MORE

answered Jun 6, 2019 in Big Data Hadoop by Kamal
526 views
+10 votes
11 answers

Hadoop “Unable to load native-hadoop library for your platform” warning

modify the glibc version.CentOS provides safe softwares ...READ MORE

answered Sep 10, 2018 in Big Data Hadoop by bug_seeker
• 15,510 points
31,573 views