Toad World Blog

How to Write a Cross-Connection Query Correctly

Apr 15, 2019 3:39:13 PM by Winnie Wu

Many people encounter problems when using the Cross-Connection Editor or Cross-Connection Query Builder to write a query. Sometimes they ask questions such as: What kind of SQL syntax should I use?  Can I use the target database's function in the query? These are all good questions. This article provides information that will help you understand how to use the correct syntax when writing a query in the Cross-Connection Editor or Query Builder.

1. Syntax for identifiers and strings in a cross-connection query

Identifiers (e.g., schema name, connection name, table name, owner name, alias name, etc.)

Use the backtick (backquote) character or nothing: `xxx` or xxx

(If xxx has a space in it, you must use the backtick character: `xxx`)

For example: select col from `some_connection`.`some_schema`.`table1`

String

Enclose the string in single quotes: 'xxx'

(Note: "xxx" is NOT supported)

For example: select `col` from `some_connection`.`some_schema`.`table1` where `col` = 'abc'

Note: Do not use double quotes ("xxx") in a cross-connection query.

 

2. What syntax should I use?

In most cases, you can use MySQL syntax. But in some cases, it is recommended or even required to use the target database's native syntax. It depends on how you write the query, what kind of target data source you are using, as well as some other factors.

To make things simple, this article provides several rules for you to follow. Now let's start from the simplest query: a single-connection query.

2.1 Single-connection query

You may write a query in which the tables are all from a single data source

Typical single connection query example

select col_1,col_2

from oracle_conn.test_db1.table_a A join oracle_conn.test_db2.table_b B

on A.id = B.id

( note:  all databases and tables are from the same connection: oracle_conn )

 

Rule 1: (Single-Connection) As long as the target data source supports SQL, an attempt is made to pushdown the whole query to the remote data source and execute the query there. So the target database's SQL syntax is always recommended.

That is to say, if the target data source is Oracle, feel free to use Oracle functions in your query, 

See an example

CCQimage1

Applied datasources
File Data Source CSV File, EXCEL, ACCESS
Relational Data Source Amazon Redshift, Greenplum, MySQL, Oracle, SQL Server, SQL Azure, ODBC Generic, DB2, IBM Netezza, MariaDB, SQL Azure, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, SAP SQL Anywhere, Teradata, Vertica
Business Intelligence Salesforce
NoSQL Apache Hive

 

Rule 2:  (Single-Connection) If the target data source is SQL Server, Oracle or Salesforce, you may even be allowed to use limited MySQL syntax in your query.

This limited MySQL syntax includes some commonly-used MySQL functions, "limit xxx," and "order by xxx," all of which are listed in Limited MySQL Syntax at the end of this article. Feel free to use them(no matter if they are marked by (tick) or not)  for the specified data sources. If you use a function that is NOT on this list, the function is not translated. It is treated as the target database's syntax and is pushed down.

Applied datasources
Relational Data Source Oracle, SQL Server, SQL Azure, ODBC Generic
Business Intelligence Salesforce

Note: The data sources above which are connected by Generic ODBC are not included.

Example

CCQimage2

Background Knowledge

You may be curious about how we can achieve this.

Answer:

1) The MySQL syntax is translated (from MySQL syntax to target database's syntax), which enables the whole query to be pushed down to the remote data source. -- This is not only for the convenience of users who are familiar with MySQL syntax, but it also improves performance. See example 2.

2) For the syntax on this list that has not been translated, please note the following:

- Some functions or syntax is already supported by the target data source, and we can detect these. So in these cases, push down of the query would also be enabled.

- Because translation requires a large effort, only the most commonly-used syntax and functions are covered. For other functions in the list that have not yet been translated, all table data is fetched from the remote data source and then filtered locally. -- This method does not provide optimal performance, but for users familiar with MySQL only, it is quite convenient.

Rule 3: (Single-Connection) For data sources that don't support SQL, use MySQL syntax.

Applied datasources
Business Intelligence Generic OData, Google Analytics, SSAS, Oracle BI, SharePoint, SAP
NoSQL DynamoDB, MongoDB, Cassandra, HBASE, Impala, Azure Table Service
Example

CCQimage3

Tip: In order to improve query performance, please specify the column names that you really need to use and add some filters.

 

2.2. Cross-connection query

Users more often open the Cross-Connection Query Builder or Editor to do a query that uses multiple connections rather than a single connection.

Typical multiple connection query

select col_1,col_2

from mysql_conn.test_db1.table_a A join mongo_conn.test_db1.table_b B

on A.id = B.id

(Note:  table_a is from a mysql table, table_b is from a mongo collection. They are from different connections.)

It's a little more complex, but some good examples can help you understand the rules.

 

Rule 4: (Multiple-Connection) The outer query of the "join query" should use MySQL syntax.

See an outer query

CCQimage4

Examples

example 1:

CCQimage5

example 2:

CCQimage6

Background knowledge

The embedded hub that does the cross-connection query is actually a MySQL plugin engine. It fetches data from the different data sources separately, and then finally does the join operation in the embedded hub (MySQL). So the outer query (final query) of the join query must use MySQL syntax.

Tip: If you really need to use the target database's syntax, reconstruct the query such that you use the target database's syntax in a subquery. See Question 1 of 3.Troubleshooting.

 

 

Rule 5: (Multiple-Connection) In the subquery which can be pushed down to the remote database, try to use the target database's syntax.

See a sub-query

CCQimage7

Examples

CCQimage8

Background knowledge

In order to improve performance, the subquery will always be pushed down to the remote data source if possible.

Tips:   1) If the subquery is a single-connection query, use syntax according to the rules in Single connection query. If not, use MySQL syntax.

           2) How do you check whether or not a subquery can be pushed down?

               Answer: All tables in the query must be from a single connection and the target data source must support SQL.

 

 

Rule 6: (Multiple-Connection) Check queries in an embedded query to see if each can be pushed down on its current level, without worrying about its subqueries or outer queries. If the query can be pushed down, try to use the target databases's syntax.

See an embedded query

CCQimage9

Examples

example 1:

CCQimage10

 

example 2:

CCQimage11

Background knowledge:

In order to improve performance, queries in an embedded query will always be pushed down from inner to outer, if possible.

Tip: If the current-level query is a single-connection query, use syntax according to the rules in Single connection query. If not, use MySQL syntax.

 

3. Trouble shooting

Question 1: In the outer query of a join query, I need to apply an oracle function on one selected column which is got from an oracle table , what should I do?

Answer: Reconstruct your join query such that the target databases's function on the selected column is included in a subquery.

For example, if you intend to write a query like this: 

                     CCQimage12

Then we would suggest you reconstruct your query like this:

CCQimage13

Question 2: How do I know if a query or a subquery will really be pushed down?

Answer: If the query or sub-query is a single-connection query, and the target data source supports SQL, an attempt is always made to push the query down to the remote data source. However, sometimes you might use MySQL syntax which has not been translated to the target databases's syntax, in this case, pushdown would not happen, all of the table data will be fetched from the remote table and the query will be executed locally.

 

A simple way to learn if a query is actually pushed down or not is to execute the query and then check the "Explain Plan."

For example, if the whole query is pushed down, you will see "Push-Down Query" in the Explain Plan tab.

CCQimage14

If a query is not pushed down, the query is finally executed locally, and you will see "Data Hub Query" in the Explain Plan tab.

CCQimage15

 

To check if a subquery is pushed down, you can also check the "Explain Plan" tab. The following example shows that the subquery was pushed down first, and then the join operation was done locally.

CCQimage16

Question 3: How do I specify a query object in Cross-connection query editor ?

Answer: A query object name is made up of several components: connection name, user/schema name, and table name. The format of the name and the number of components varies depending on the data source. The example below is an oracle connection object.

CCQimage17

 

The best way to get the exact object name is to open a Cross-Connection SQL Editor window and drag the table you want to query to the Editor. In the menu that opens, select "Insert Name." This action inserts the exact name.

 

 

4. Useful Tips

1) In order to improve query performance, please specify the column names that you really need to use and add some filters. Otherwise, an attempt is made to fetch all data from the remote table.

2) If you find it difficult to use the Cross-Connection SQL Editor to write a query, you can use the Cross-Connection Query Builder. The Query Builder allows you to build a query without writing a SQL statement.

Additional Information

Limited MySQL syntax list used by target data source: SQL Server, Oracle, Salesforce

MySQL functions: 

string functions time functions date functions miscellaneous functions aggregate functions
function name translation has been done for: function name translation has been done for: function name translation has been done for: function name translation has been done for: function name translation has been done for:
Oracle SQL Server Salesforce Oracle SQL Server Salesforce Oracle SQL Server Salesforce Oracle SQL Server Salesforce Oracle SQL Server Salesforce
SUBSTRING (tick) (tick)   NOW (tick) (tick)   YEAR greencheckmark   (tick) ISNULL greencheckmark greencheckmark   COUNT     (tick)
SUBSTR     (tick) CURRENT_TIMESTAMP (tick) (tick)   QUARTER (tick) (tick) (tick) IFNULL greencheckmark greencheckmark          
LENGTH     (tick) CURRENT_DATE (tick) (tick)   MONTH (tick)   (tick) TRIM greencheckmark greencheckmark          
LOCATE (tick) (tick)   CURRENT_TIME (tick) (tick)   DAY (tick)     CONVERT_TZ              
CONCAT   (tick)   CURDATE (tick) (tick)   DAYOFMONTH (tick) (tick) (tick)                
LEFT (tick)     CURTIME (tick) (tick)   HOUR (tick) (tick) (tick)                
RIGHT (tick)     LOCALTIME (tick) (tick)   MINUTE (tick) (tick)                  
        LOCALTIMESTAMP (tick) (tick)   SECOND (tick) (tick)                  
                DAYOFYEAR (tick) (tick)                  
                YEARWEEK (tick)                    
                WEEKOFYEAR (tick)                    
                WEEK (tick)                    
                WEEKDAY (tick)                    
                DAYOFWEEK (tick)                    
                MICROSECOND (tick) (tick)                  
                MONTHNAME   (tick)                  
                DAYNAME   (tick)                  
                TO_DAYS (tick) (tick)                  
                TO_SECONDS (tick) (tick)                  
                DATE_FORMAT                      
                ADDDATE (tick) (tick)                  
                ADDTIME (tick) (tick)                  
                DATE_ADD (tick) (tick)                  
                DATE_SUB (tick) (tick)                  
                DATEDIFF (tick) (tick)                  
                SUBDATE (tick) (tick)                  
                SUBTIME (tick) (tick)                  
                TIMESTAMPADD (tick) (tick)                  
                TIMESTAMPDIFF   (tick)                  
                DATE (tick) (tick) (tick)                
                TIME (tick) (tick)                  
                MAKEDATE (tick) (tick)                  
                MAKETIME (tick) (tick)                  
                TIMESTAMP (tick) (tick)                  

limit xxx:   translation has been done for SQL Server, Oracle

order by xxx:  translation has been done for SQL Server

Tags: Toad Data Point Toad Intelligence Central

Winnie Wu

Written by Winnie Wu

Winnie Wu is a software developer. She joined Quest in 2015 coding for Toad Intelligence Central product.