JarCastingTreasure Data JDBC Driver
NOTE: We no longer maintain td-jdbc driver for Presto now that we support native presto-jdbc drivers.
JDBC Driver for accessing Treasure Data. This works with Java 1.6 or higher.
- Download td-jdbc-jar-with-dependencies-(version).jar
- Use driver class name:
com.treasuredata.jdbc.TreasureDataDriver - Read Quick Start or General Usage
td-jdbc internally uses td-client-java, a Java client for Treasure Data.
For Maven Users
<dependency>
<groupId>com.treasuredata</groupId>
<artifactId>td-jdbc</artifactId>
<version>(version)</version>
</dependency>
Quick Start
Create a java.sql.Connection object using JDBC address jdbc:td://api.treasuredata.com/(database name).
Properties props = new Properties();
props.setProperty("user", "(your account e-mail)");
props.setProperty("password", "(your password)");
// Alternatively, you can use API key instead of user and password
// props.setProperty("apikey", "(your API key)")
// Set the other options
// Use SSL (default) or not
// props.setProperty("useSSL", "true");
// Run Hive jobs. The default is "presto"
// props.setProperty("type", "hive");
// proxy configurarion (optional)
// props.setProperty("httpproxyhost", "(proxy host)");
// props.setProperty("httpproxyport", "(proxy port)");
// props.setProperty("httpproxyuser", "(proxy username)");
// props.setProperty("httpproxypassword", "(proxy password)");
Connection conn = DriverManager.getConnection("jdbc:td://api.treasuredata.com/sample_datasets", props);
Statement st = conn.createStatement();
try {
ResultSet rs = st.executeQuery("SELECT count(1) FROM www_access");
// You can see the job ID of the query
TDResultSetMetaData rsmd = (TDResultSetMetaData) rs.getResultSetMetaData();
System.out.println("job id: " + rsmd.getJobId());
// Getting the result rows
while (rs.next()) {
int count = rs.getInt(1);
System.out.println("result = " + count);
}
rs.close();
}
finally {
st.close();
conn.close();
}
To configure td-jdbc connection parameters, use URL parameters, Properties object or System properties. The precedence of these properties is:
- Environment variable (only for TD_API_KEY parameter)
- System properties
- Properties object passed by
DriverManager.getConnection(jdbc_url, Properties) - JDBC URL parameters (e.g.,
jdbc:td://api.treasuredata.com/mydb;type=hive;useSSL=true), separated by semi-colon;
If your environment defines TD_API_KEY variable, td-jdbc uses it. For the other properties, System properties have the highest priority.
A list of JDBC Configurations
You must provide apikey property or both user (your account e-mail) and password for the authentication:
| key | default value | description |
|---|---|---|
apikey |
API key to access Treasure Data. You can also set this via TD_API_KEY environment variable. | |
user |
Account e-mail address (unnecessary if apikey is set) |
|
password |
Account password (unnecessary if apikey is set) |
|
type |
presto | Query engine. hive, preto or pig |
useSSL |
false | Use SSL encryption for accessing Treasure Data |
useapikey |
false | Use API key to access Treasure Data even if user or password is set |
httpproxyhost |
Proxy host (optional) e.g., "myproxy.com" | |
httpproxyport |
Proxy port (optional) e.g., "80" | |
httpproxyuser |
Proxy user (optional) | |
httpproxypassword |
Proxy password (optional) |
If both user and password are given, td-jdbc uses this pair instead of apikey.
You can also use td-client-java specific options.
Internals
When running a query (e.g. SELECT), the driver submits a job request to Treasure Data. td-jdbc periodically monitors the job progress and fetches the result after the job completion.
For INSERT statement, td-jdbc buffers the data into local memory, then flushes it to Treasure Data every 5 minutes, so there will be a delay until your data becomes accessible in Treasure Data.
Implementation Status
Following methods have been implemented.
java.sql.Connection
- createStatement() and createStatement(..)
- getMetaData()
- prepareStatement(..)
java.sql.Statement
- execute(..)
- executeQuery(..)
- setResultSet()
java.sql.PreparedStatement
- addBatch()
- clearParameters()
- execute()
- executeQuery()
- getMetaData()
- setBoolean(..)
- setByte(..)
- setDouble(..)
- setFloat(..)
- setInt(..)
- setLong(..)
- setShort(..)
- setString(..)
java.sql.ResultSet
- findColumn(String)
- getBoolean(..)
- getByte(..)
- getDate(int) and getDate(String)
- getDouble(int)
- getFloat(int)
- getInt(int)
- getLong(int)
- getMetaData()
- next()
- getObject(int) and getObject(String)
- getShort(..)
java.sql.ResultSetMetaData
- getColumnCount()
- getColumnDisplaySize(int)
- getColumnLabel(int)
- getColumnName(int)
- getColumnType(int)
- getColumnTypeName(int)
java.sql.DatabaseMetaData
- getCatalogs()
- getColumns(..)
- getSchemas(..)
- getTableTypes()
- getTables(..)
License
Apache License, Version 2.0
For developers
Building
You can get latest source code using git.
$ git clone [email protected]:treasure-data/td-jdbc.git
$ cd td-jdbc
$ mvn package
You will get the td-jdbc jar file in td-jdbc/target folder The file name will be td-jdbc-${jdbc.version}-jar-with-dependencies.jar. See the pom.xml file for more details.
To run production tests, write your apikey to $HOME/.td/td.conf:
[account]
user = (e-mail address)
apikey = (apikey)
password = (password)
Buidling td-jdbc with JDK7 or higher
jdbc-api-4.1.jar, which is contained in mvn-local, is necessary to build td-jdbc using an older version (4.1) of JDBC API.
Building jdbc-api-4.1.jar on Mac OS X
- Install jdk6 https://support.apple.com/kb/DL1572?locale=en_US
$ jar xvf jar xvf /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bundle/Classes/classes.jar java/sql javax/sql
$ jar cvf jdbc-api-4.1.jar java javax
$ mvn deploy:deploy-file -Durl=file://(path to td-jdbc folder)/mvn-local -Dfile=jdbc-api-4.1.jar -DgroupId=com.treasuredata.thirdparty -DartifactId=jdbc-api -Dpackaging=jar -Dversion=4.1
``
### Publish to Sonatype
The following operations basically follows [this document](https://central.sonatype.org/pages/apache-maven.html)
- Setup your Sonatype account and GPG key
- Prepare `~/.m2/settings.xml` like this
$ mvn release:clean release:prepare
$ mvn release:perform
