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
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,
|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|
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 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.
|Relational Data Source||Oracle, SQL Server, SQL Azure, ODBC Generic|
Note: The data sources above which are connected by Generic ODBC are not included.
You may be curious about how we can achieve this.
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.
|Business Intelligence||Generic OData, Google Analytics, SSAS, Oracle BI, SharePoint, SAP|
|NoSQL||DynamoDB, MongoDB, Cassandra, HBASE, Impala, Azure Table Service|
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.
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.
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.
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.
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
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:
Then we would suggest you reconstruct your query like this:
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.
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.
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.
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.
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.
Limited MySQL syntax list used by target data source: SQL Server, Oracle, Salesforce
|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|
limit xxx: translation has been done for SQL Server, Oracle
order by xxx: translation has been done for SQL Server