In this topic, you find information about how you can write custom queries for connections in Spotfire, including information about requirements and limitations that you must keep in mind.
Preparations
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.
Custom query basics
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 that is written for an 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: If you want to take a closer look at the final queries that Spotfire sends to external data sources, you can enable logging and study the connector query log.
The custom query will be evaluated each time a regular query is performed from the Spotfire application. If the query is used in-database, then the custom query will be executed each time a visualization or filter is updated. For the in-memory option, that is, when data is loaded into the Spotfire data engine, the query will only be executed when data is imported or explicitly refreshed.
Requirements and limitations for custom queries
Requirements
The custom query must be written in the language that the database understands. For example, for a Microsoft SQL Server database, you write your custom query in the Microsoft SQL Server dialect of SQL.
The custom query must be possible
to execute as a subquery in a FROM clause. See the official documentation
for the external data source for more information.
Tip: 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 “;”
Limitations
Stored procedures are not supported in custom queries.
Common table expressions (CTE’s) are not supported, because they require the WITH keyword.
Data Types
When you create a custom query you should take care only to include columns from the database of 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 happens to be included in the query it will be marked as invalid in the verification step and it will not be possible to use the resulting table until the issue has been resolved. This is done by either rewriting the query so the unsupported columns are excluded, or by removing the column. 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.
Parameters
If you want the query to retrieve different results depending on the value of one or more variables, you can set up the query to use parameters. A parameter can be mapped to, for example, a property control in the text area.
A parameter is referred to using the syntax ?parametername where parametername is a name that you select for your parameter and define the data type for on the Parameters tab.
Example of a query using a parameter:
SELECT Col1, Col2 FROM Table WHERE Col1 > ?Param1
Important: You cannot use parameters
to control what database table to select data from. For example, it is
not possible to write a custom query like this:
SELECT * FROM ?parameter
The reason is that a parameter cannot store table identifiers. Strings
and table identifiers are typically handled differently in SQL queries.
In a lot of cases, multiple database data types will map to a single Spotfire data type. As an example, when connecting to Microsoft SQL Server the data types varchar, nvarchar, text, ntext, etc. will all map to the Spotfire data type String. In those cases, when declaring the parameter type, it is also possible to specify the actual type in the external system. That is, the parameter is of the Spotfire data type String but in the external database it is an nvarchar. While Spotfire cannot automatically map the external type of the parameter, a manual specification of the external type can make the query more effective. If there is a one-to-one mapping between the Spotfire type and the external type, then the external type drop-down list will be disabled.
Provided that you and the end users of the analysis are logged into a Spotfire Server, you can also use one or more of the predefined parameters for personalized queries; current_user, current_groups and current_user_domain, which become enabled by selecting the "Use parameters for personalized queries (users and groups)" check box. These parameters work the same way as they do when using them from the Information Designer, which is described under Personalized Information Links.
Once the check box is enabled you can use the personalized query parameters in your custom query. The type of these parameters is always String in Spotfire and the external type is the default value, e.g., NVARCHAR in SQL Server.
Security
To create a custom query, you must have the "Custom Query in Connections" license.
No other user will be able to execute a custom query that you create, unless two conditions are fulfilled:
Firstly, you must save the custom query to the library as part of an analysis, or as part of a data connection.
Secondly, you must be a member of the "Custom Query Author" group, which means that you are authorized to create 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.