Articles in this section

See more

How to Import Data from SQL

  • Updated
Follow

 

Introduction

There are many ways to get data into Q. This is the way to access data from an SQL database.

Requirements

  • The database must be an SQL database.
  • Any machine opening the Q project must be able to connect (over the network) to the database server. This may require your network administrators to reconfigure firewalls.
  • You must know:
    • type of database (brand, e.g. Microsoft SQL Server)
    • server name
    • database name
    • user name
    • password
  • The ADO.NET data provider appropriate to your brand of database must be installed on any machines that will open the project. Some data providers are included with Q (Microsoft SQL Server, ODBC, OLE DB), and you can install Oracle and others on your machine. Q supports Microsoft SQL Server and MySQL, but we can add others on request.
  • You need to know how to to write SQL queries, or have someone who can help you.

Method

Q is able to read raw data from SQL databases. Add an SQL database to your project using File > Data Sets > Add to Project > From Database (SQL).

How to add an SQL database to an existing project

  1. Open the existing project.
  2. File > Data Sets > Add to Project > From Database (SQL).
  3. Fill in the SQL Configuration (see the rest of this page for instructions).
  4. Press OK.
  5. Set up your project.

Installing Data Providers

Q can use any ADO.NET data provider you install on your machine. Here are some we have tried.

Microsoft SQL Server You don't need to install anything. This is already part of Q.
Oracle Download the "64-bit ODAC" (or "32-bit ODAC" if on a 32-bit Windows machine) from Oracle and install it. Use the Oracle.DataAccess.Client data provider. (An Oracle data provider is supplied with Q, but it will not work on 64-bit machines and may not work with newer Oracle databases.)
MySQL Download and install the latest MySQL Connector/Net  
Amazon Redshift Download and install the ODBC driver (you mostly likely need the 64-bit version). Use the System.Data.Odbc data provider. Your connection string should look like this: Driver={Amazon Redshift (x64)}; Server=XXX.redshift.amazonaws.com; Database=XXX; UID=XXX; PWD=XXX; Port=5439

 

Setup

Name
The name you want to use to refer to this data (this can be anything you want). 
Data provider
The name of the ADO.NET data provider to use to connect to your database.
This must match the type of database being used. e.g. System.Data.SqlClient for SQL server.
Connection string
Identifies the server, user name, password, etc. required to get to your database.

e.g.
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=True;
You can find more examples at http://www.connectionstrings.com.
SQL command
An SQL query that will return the data you want to use in Q.

e.g. This query finds the number of completed respondents for the last 40 days, broken down by date (MySQL): 
select date(Updated), count(*) as CountInQuota
  from Respondent
 where Updated > adddate(curdate(), -40) and Progress = 'Q'
 group by Date(Updated);

e.g. This query extracts age from a database, matching on customer IDs. In this example, the join table is set to "#CUSTIDS". 
select C.Age
  from Customer C
 inner join #CUSTIDS I on C.CustomerID = I.ID;
Maximum cases
Limits the number of rows of data that will be fetched from the database.
This helps protect you from queries that explode into enormous numbers of rows, and clog up both Q and your database.
Automatically refresh every
The number of hours old that your data may be before Q will automatically refresh it. The dashboard will slow down while refreshing data, so don't set this lower than necessary.

How Q Interprets the Data

Each output field becomes a variable within Q. You can control the name of the variable by using as (see examples above). Q will automatically recognize date and date/time columns as Q dates, nvarchar/char as text, and everything else as numeric data. Some column types (e.g. binary data) cannot be used by Q, and will cause an error.

Suggestions

  • A password is usually included in the connection string, so have your database administrator set up a database user account that is only able to read data, and only the data you need.
  • Don't experiment with your SQL if you are connecting to an important database - have a database administrator help you.
  • Only select the columns you need in Q, don't use select *. This will speed up your queries, reduce Q memory usage, and avoid problems that might occur with data Q cannot understand.
  • While it is not possible to list the supported data types for every database vendor, Q will generally accept text, numeric and date/time data. e.g. VARCHAR, CHAR, NUMERIC, DATETIME.
  • Use where clauses to fetch only the rows you need.
  • Q provides no help for getting your SQL right. Therefore get your query working in a proper database tool first, and only then paste it into Q.

See Also

MySQL Worked Example

How to Upload Join IDs for SQL Data

How to Connect to a MySQL Database in Q

How to Set Up Files With No Metadata

 

Related articles