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 LicensingTerms and Agreement |
DownloadQuandl JDBC driver |
Getting StartedGet 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:
- Download the UnityJDBC Driver distribution which contains the Quandl JDBC Driver and sample JDBC code for querying Quandl.
- 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
Attribute Values Description 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 - 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
- Create a new instance of the JDBC Driver for Quandl (optional).
- 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.
- Create a statement and submit a query.
- Print out your results.
- Close the statement and connection.
Class.forName("quandl.jdbc.QuandlDriver");
String url = "jdbc:quandl://www.quandl.com/api/v1?" + "schema=quandl_schema.xml&tables=FRED/CAPOP"; con = DriverManager.getConnection(url, "", "API_KEY");
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);
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(); }
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
- Setup Instructions for using the Quandl JDBC Driver with SQuirreL SQL - the Quandl JDBC driver is supported in the open source SQuirreL SQL query software allowing SQL queries on Quandl