Spotfire® User Guide

Custom queries

A custom query is a way to select data from an external data source in a data connection, with the use of your own custom database query. It is an alternative to selecting tables and columns in the point-and-click Select data or Views in connection dialogs. With a custom query, you can put your SQL skills to use and make a very specific data selection. The result of a custom query is a database table that you can access like a data table in Spotfire

On this page

Adding a custom query in a data connection

In an installed Spotfire client, you can add a custom query to your data connection when you select data in the Views in connection dialog.

Before you begin

To be able to add and edit custom queries in data connections, be aware of the following requirements:
  • To add and edit custom queries, you must use an installed Spotfire client.
  • Not all data connectors support using custom queries.
  • The Custom query in connections license is required to add and edit custom queries. To be able to trust custom queries, so that other users can open them, you must also be a member of the user group Custom Query Author.
  • You write custom queries in the query language of the external database. To learn more, see Writing custom queries.

Procedure

  1. Create a new data connection (Files and data > Connect to), or edit an existing data connection in your analysis or library.
  2. In the Select data or Views in Connection dialog, click Add custom query ( or Custom Query > New Custom Query).

  1. In the Custom Query dialog, enter a Query name.
  2. In the Query field, type your database query.
  3. When you have completed your query, click Verify to test the query and fetch the Result columns. If there are any errors in the query, information about the error is shown. Fix the errors and run Verify again to make sure the issues are resolved.
  4. Go through the Result columns and make sure that all the desired result columns are listed and that they are of the correct data type.
    Tip: Editing result columns

    Only the columns in the Results column list are included in the final data table, even if your query selects more columns. You can exclude unwanted columns from the query with the Delete button, without having to change the query. You can also change the order of the columns with the Move Up and Move Down buttons.

    The name and data type of columns must match the name and data type from the external data source. To change the name of a column in the resulting custom query table, do so in the Views in Connection dialog, after you have finished creating the custom query.

  5. To finish your custom query, and add it to your data connection, click OK.

Results

The custom table based on the custom query is added list of selected tables/views in the connection. You can work with the custom table like other tables. For example, you can combine it with other tables with structural relations, and you can either import or keep the data as external in-database data.

Writing custom queries

When you write a custom query, there are requirements, limitations, and general guidelines to keep in mind.

When you write a custom query, you write the query in the language of the external data source. This means that the way you write the query is dependent on the type of data source you are connecting to. For example, for a Microsoft SQL Server database, you would write your custom query in the Microsoft SQL Server dialect of SQL.

When you use a custom query to access data in an external data source, your custom query is wrapped as a subquery in a FROM clause of the final query that Spotfire sends to the external data source. For example, below is a custom query for a Microsoft SQL Server connection: 
SELECT Country,AVG(Sales) AS AvgCountrySales FROM "World Sales" GROUP BY Country
If you import the view resulting from this custom query as a data table in Spotfire, Spotfire sends the query below to the external data source. Notice how the original custom query is included as a subquery in the FROM clause of the SELECT statement: 
SELECT [SpotfireCustomQuery1].[Country] AS [Country], [SpotfireCustomQuery1].[AvgCountrySales] AS [AvgCountrySales] FROM (SELECT Country,AVG(Sales) AS AvgCountrySales FROM "World Sales" GROUP BY Country) AS [SpotfireCustomQuery1]
Tip: To inspect the queries that Spotfire sends to external data sources, you can enable logging and review the connector query log.

The custom query is evaluated each time a regular query is performed from Spotfire. If you use the custom query for an in-database (external) data table, the custom query is run each time a visualization or filter is updated. For in-memory (imported) data tables, that is, when data is loaded into the Spotfire data engine, the query will only run when data is imported or explicitly refreshed.

Query requirements and limitations

  • Queries are written in the language of the external database. For example, for a Microsoft SQL Server database, you would write your custom query in the Microsoft SQL Server dialect of SQL. For specific information on how to write queries for a certain data source type, always refer to the official documentation for that external data source. Capabilities, limitations and syntax rules is dependent on the type of data source you are connecting to.
  • It must be possible to run the custom query as a subquery in a FROM clause. See the official documentation for the external data source for more information.
    Note: Subqueries in FROM clauses are sometimes referred to as derived tables.
  • The custom query must consist of a single statement. You cannot chain multiple statements together separated by a semicolon character ";".
  • Stored procedures are not supported in custom queries.
  • Common table expressions (CTEs) are not supported, because they require the WITH keyword.

Data types

When you create a custom query, only include columns with data types that are actually supported by Spotfire. See the documentation for the connector you are currently using for a list of supported data types.

If an unsupported column is included in the query, it is marked as invalid in the verification step, and you cannot use the resulting table. To resolve such issues, either rewrite the query to exclude the unsupported columns, or remove the columns from the resulting table. You can always assign an arbitrary data type to a custom query column. However, there is no guarantee that the resulting Spotfire table for such a custom query will work.

Security — Trusting custom queries

No other user will be able to run a custom query that you create, unless two conditions are fulfilled:

  • You must save the custom query to the library as part of an analysis, or as part of a data connection.
  • You must be a member of the Custom Query Author group, which means that you are authorized to create and trust custom queries on behalf of other users.

It is still possible to share a custom query you have created with another user, but that user must review and trust the custom query. A user can trust a custom query by opening the Custom Query dialog, examining the custom query text, and then clicking OK.

Copyright © Cloud Software Group, Inc. All rights reserved.