JDBC Driver for Quandl

The JDBC driver for Quandl runs SQL queries against Quandl for any Java/JDBC application. SQL support includes functions, expressions, aggregation, and joins.


Simple Licensing

Terms and Agreement

Download

Quandl JDBC driver

Getting Started

Get started in 5 minutes



Features

  • Access data in Quandl from any Java/JDBC program.
  • Manipulate and control data using standard SQL functions not natively supported by Quandl.
  • Perform SQL joins across Quandl tables. Join and merge data with other relational and NoSQL systems.
  • Simplifies querying and extracting data compared to using the Quandl REST API. Increases your development efficiency and simplifies maintenance.

How it works

  • The SQL query is validated and translated into a Quandl REST request.
  • Metadata is exposed to relational systems through JDBC by building a schema by parsing table descriptions provided by Quandl.
  • SQL features not natively supported by Quandl are executed using the UnityJDBC virtualization engine.

Getting Started

Start using the JDBC Driver for Quandl in 5 minutes:

  1. Download the UnityJDBC Driver distribution which contains the Quandl JDBC Driver and sample JDBC code for querying Quandl.
  2. Configure the connection. The JDBC Driver for Quandl can be used with any query or reporting software that supports JDBC. The connection information is:
    	JDBC Driver class name:  quandl.jdbc.QuandlDriver
    	URL format:              jdbc:quandl://www.quandl.com/api/v1
    As part of the connection string, connection arguments can be passed into the driver that will affect its behavior.
    AttributeValuesDescription
    debug true, false The debug property will cause the driver to print out debug information to the console during its operation.

    password <password> Quandl API Key found under Quandl account settings . If not given, Quandl will limit the number of requests to 50 per day. (optional)

    encoding utf-8 Character encoding used.

    schema <file_location> File location of schema. Default schema location is quandl_schema.xml in the current directory.

    rebuildschema true, false If true, rebuilds schema for connection. If false, uses existing cached schema if available. Uses location provided in schema property.

    tables comma-separated list of Quandl tables A list of tables to be used for the initial creation of the schema. Tables are named by their Quandl code. Example: tables=FRED/CAPOP,ODA/USA_NGDP
  3. Run your queries. Select a dataset, and use the dataset's "Quandl Code". For example:
    SELECT *
    FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"

    If the submitted query cannot be natively handled by the JDBC Driver for Quandl (such as for queries containing JOIN, GROUP BY or HAVING clauses), the query will be promoted to UnityJDBC for processing. UnityJDBC will parse the query into subqueries that will then be run on the specific Quandl tables and process the intermediate results to produce the final result.

Example Code Walkthrough

  1. Create a new instance of the JDBC Driver for Quandl (optional).
  2. 	Class.forName("quandl.jdbc.QuandlDriver");
  3. Connect to the URL. A schema will be created and cached using the tables listed. Control the schema location using the schema URL parameter. Note that caching schemas is recommended for good performance.
  4. 	String url = "jdbc:quandl://www.quandl.com/api/v1?"
    		   + "schema=quandl_schema.xml&tables=FRED/CAPOP";
    	con = DriverManager.getConnection(url, "", "API_KEY");
  5. Create a statement and submit a query.
  6. 	stmt = con.createStatement();
    	String sql = "SELECT Date, Value as California_Population_In_Thousands"
    		   + "FROM \"FRED/CAPOP\""
    		   + "WHERE Date > '2001-01-01'"
    		   + "ORDER BY Date ASC LIMIT 3;";
    	rst = stmt.executeQuery(sql);
  7. Print out your results.
  8. 	ResultSetMetaData meta = rst.getMetaData();
    	int numColumns = meta.getColumnCount();
    	System.out.print(meta.getColumnName(1));
    	for (int j = 2; j <= meta.getColumnCount(); j++)
    		System.out.print(", " + meta.getColumnName(j));
    	System.out.println();
    	while (rst.next())
    	{
    		System.out.print(rst.getObject(1));
    		for (int j = 2; j <= numColumns; j++)
    			System.out.print(", " + rst.getObject(j));
    		System.out.println();
    	}
  9. Close the statement and connection.
  10. 	rst.close();
    	stmt.close();
    	con.close();

Features and SQL Support for the JDBC Driver for Quandl

The following table shows which SQL queries can be handled directly by the JDBC driver for Quandl. Queries that cannot be run natively through the JDBC driver for Quandl will be automatically promoted to the UnityJDBC virtualization engine. Note: The 30-day trial version returns unlimited results. After the 30-day trial expires, UnityJDBC queries will return up to 100 results (purchase a license here).

Important Notes:

  • Case-sensitive identifiers: SQL identifiers (table and field names) are not case-sensitive. Use delimited-identifiers (with double-quotes) to force case-sensitivity in SQL and always use double-quotes for SQL reserved words (e.g. "select").

  • SQL functions: Common SQL functions including string functions (concat, substr, instr), date functions (e.g. dateadd, datediff, day), trigonometry functions, mathematical functions (e.g. abs), bit functions, and others are supported. Complete list of SQL functions supported.

  • JDBC DatabaseMetaData and ResultSetMetaData Support: The JDBC Driver for Quandl supports the JDBC API allowing interoperability with database software using SQL and JDBC. This includes support for DatabaseMetaData and ResultSetMetaData. A schema is built on connection and can be cached (see Connection Parameters for more details). For DatabaseMetaData, common API methods used include getTables, getColumns, getPrimaryKeys, getDatabaseMajorVersion, among others.

Feature SQL Example JDBC Driver for Quandl UnityJDBC Virtualization Support
SELECT Clause
Select all fields from one table
SELECT *
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Select a list of fields from one table
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Select with field aliases
SELECT DATE d, VALUE AS val
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Select expressions
SELECT DATE d, VALUE+1 AS val
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Select distinct
SELECT DISTINCT VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
FROM Clause
One table query
SELECT *
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Multiple table query
SELECT U.Date, U.Value, P.Value
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE" U,
       "WORLDBANK/POL_SP_DYN_IMRT_IN" P
WHERE  U.Value = P.Value
Inner join clause
SELECT U.Date, U.Value, P.Value
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE" U INNER JOIN
       "WORLDBANK/POL_SP_DYN_IMRT_IN" P
       ON  U.Value = P.Value
Left outer join clause
SELECT U.Date, U.Value, P.Value
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE" U LEFT OUTER JOIN
       "WORLDBANK/POL_SP_DYN_IMRT_IN" P
       ON  U.Value = P.Value
Right outer join clause
SELECT U.Date, U.Value, P.Value
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE" U RIGHT OUTER JOIN
       "WORLDBANK/POL_SP_DYN_IMRT_IN" P
       ON  U.Value = P.Value
Full outer join clause
SELECT U.Date, U.Value, P.Value
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE" U FULL OUTER JOIN
       "WORLDBANK/POL_SP_DYN_IMRT_IN" P
       ON  U.Value = P.Value
WHERE Clause      *Note: Quandl only supports WHERE on the Date column*
Equality (=) comparison on Date
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE = '2000-12-31'
Equality (=) comparison not on Date
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  VALUE = 26.0
Greater than equal (>=) comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE >= '2000-12-31'
Greater than (>) comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE > '2000-12-31'
Less than equal (<=) comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE <= '2000-12-31'
Less than (<) comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE < '2000-12-31'
Not equal (!= or <>) comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE != '2000-12-31'
Like comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE LIKE '2000%'
Two attribute comparison
SELECT DATE, OPEN, High
FROM   "OFDP/FUTURE_BO1"
WHERE  OPEN = High
Negation
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  NOT (DATE = '2000-12-31')
AND and OR
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE = '2000-12-31' AND (VALUE = 26.0 OR VALUE = 31.2)
NOT LIKE comparison
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE NOT LIKE '2000%'
Date and Timestamp Support      *Note: Quandl only supports WHERE on the Date column*
DateTime comparison with Current Time
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE < CURRENT_TIMESTAMP()
DateTime comparison with Date Constant
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE < '2000-12-31 05:30:00'
group by Clause
Group by one attribute
SELECT   OPEN, COUNT(*) AS cnt
FROM     "OFDP/FUTURE_BO1"
GROUP BY OPEN
Group by multiple attributes
SELECT   OPEN, High, COUNT(*) AS cnt
FROM     "OFDP/FUTURE_BO1"
GROUP BY OPEN, High;
Aggregate Functions
Count(*)
SELECT COUNT(*)
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Count(attribute)
SELECT COUNT(VALUE)
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Maximum
SELECT MAX(VALUE)
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Minimum
SELECT MIN(VALUE)
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Summation
SELECT SUM(VALUE)
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Average
SELECT AVG(VALUE)
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
Having Clause
Having clause
SELECT   OPEN, COUNT(*) AS cnt
FROM     "OFDP/FUTURE_BO1"
GROUP BY OPEN
HAVING   COUNT(*) > 30
Order by Clause      *Note: Quandl only support ORDER BY on the Date column*
Order by Date
SELECT   DATE, VALUE
FROM     "WORLDBANK/USA_SP_DYN_SMAM_FE"
ORDER BY DATE ASC
Order by one attribute
SELECT   DATE, VALUE
FROM     "WORLDBANK/USA_SP_DYN_SMAM_FE"
ORDER BY VALUE ASC
Order by multiple attributes
SELECT   DATE, VALUE
FROM     "WORLDBANK/USA_SP_DYN_SMAM_FE"
ORDER BY DATE DESC, VALUE ASC;
Order by expression
SELECT   DATE, VALUE
FROM     "WORLDBANK/USA_SP_DYN_SMAM_FE"
ORDER BY VALUE+1 ASC
Limit/Offset Clause
Limit clause
SELECT DATE, OPEN
FROM   "OFDP/FUTURE_BO1"
LIMIT  5
Offset clause
SELECT DATE, OPEN
FROM   "OFDP/FUTURE_BO1"
LIMIT  5
OFFSET 10
Union Clause
Two query union
SELECT DATE, VALUE
FROM   "WORLDBANK/USA_SP_DYN_SMAM_FE"
WHERE  DATE = '2009-12-31'
UNION
SELECT DATE, VALUE
FROM   "WORLDBANK/POL_SP_DYN_IMRT_IN"
WHERE  VALUE > 45

Setup Instructions