How to Import Data by Connecting to an SQL Database

Requirements

• An SQL database to connect to. In this example, we are using a public MySQL database called Ensembl.
• Displayr must be able to connect (over the public internet) to the database server. This may require your network administrators to reconfigure firewalls. There is no predictable outbound IP address used by Displayr.
• You must know:
  • Type of database (brand, e.g. Microsoft SQL Server)
  • Server name
  • Database name
  • User name
  • Password
  • You need to know how to write SQL queries or have someone who can help you.

Method

1. Select Data Sets > Plus (+) > SQL.

2. Enter a Name for your data set. This is whatever name you will use to refer to this data.

3. Select the appropriate Data provider. This must match the type of database being used. See Data Providers below. In this case, we will select MySql.Data.MySqlClient. If your provider is not listed, please contact Support.

4. Use the correct Connection string and then press Test to check the connection is OK. Your connection string will contain all the necessary parameters, such as server, database, user id, and password similar to below:

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=True;

You can find more examples of SQL connection strings here. In this example, we will use:

Server=ensembldb.ensembl.org; Database=aedes_aegypti_core_48_1b; UID=anonymous;Port=3306

5. Click the Test button to make sure you get the "Connection OK" message. 

6. OPTIONAL: Adjust Maximum cases to limit the number of records pulled from the data. This helps protect you from queries that explode into enormous numbers of rows and clog up both Displayr and your database.

7. Set update cycle via Automatically refresh every X hours. This is the number of hours old that your data may be before Displayr will automatically refresh it. The dashboard will slow down while refreshing data, so don't set this lower than necessary.

8. Write your SQL command that will return the data you want to use in Displayr in the box provided.

Here we will pull data from specific fields from this database:

SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,TABLE_TYPE,CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'acanthochromis_polyacanthus_core_94_1'

9. Press OK.

Data Providers

Each database vendor requires a different data provider. The table below maps vendors to a data provider.

Contact Displayr support if you need a data provider other than those listed above.

How Displayr Interprets the Data

Each output column becomes a variable within Displayr. You can control the name of the variable by using as (see examples above). Displayr will automatically recognize date and date/time columns as Displayr dates, nvarchar/char as text, and everything else as numeric data. Some column types (e.g. binary data) cannot be used by Displayr, 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 attaching to an important database - have a database administrator help you.
• Only select the columns you need in Displayr, don't use select *. This will speed up your queries and avoid problems that might occur with data Displayr cannot understand.
• While it is not possible to list the supported data types for every database vendor, Displayr will generally accept text, numeric, and date/time data. e.g. VARCHAR, CHAR, NUMERIC, DATETIME.
• Use whereclauses to fetch only the rows you need.
• Displayr 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 Displayr.

Next

Once you've imported your data, it's always a good idea to Check Your Data.