Optimizing SQL Query of Data of Multiple Sources

Updated on 2022-12-14 GMT+08:00

View PDF

Scenario

This section describes how to enable or disable the query optimization for inter-source complex SQL.

Procedure

(Optional) Prepare for connecting to the MPPDB data source.
If the data source to be connected is MPPDB, a class name conflict occurs because the MPPDB Driver file gsjdbc4.jar and the Spark JAR package gsjdbc4-VXXXRXXXCXXSPCXXX.jar contain the same class name. Therefore, before connecting to the MPPDB data source, perform the following steps:
1. Move gsjdbc4-VXXXRXXXCXXSPCXXX.jar from Spark. Spark running does not depend on this JAR file. Therefore, moving this JAR file to another directory (for example, the /tmp directory) will not affect Spark running.
  1. Log in to the Spark server and move gsjdbc4-VXXXRXXXCXXSPCXXX.jar from the ${BIGDATA_HOME}/FusionInsight_Spark2x_8.1.2.2/install/FusionInsight-Spark2x-3.1.1/spark/jars directory.
  2. Log in to the Spark client host and move gsjdbc4-VXXXRXXXCXXSPCXXX.jar from the /opt/client/Spark2x/spark/jars directory.
2. Obtain the MPPDB Driver file gsjdbc4.jar from the MPPDB installation package and upload the file to the following directories:
  - /${BIGDATA_HOME}/FusionInsight_Spark2x_8.1.2.2/install/FusionInsight-Spark2x-3.1.1/spark/jars on the Spark server.
  - /opt/client/Spark2x/spark/jars on the Spark client.
3. Update the /user/spark2x/jars/8.1.2.2/spark-archive-2x.zip package stored in the HDFS.
  1. Log in to the node where the client is installed as a client installation user. Run the following command to switch to the client installation directory, for example, /opt/client:
    cd /opt/client
  2. Run the following command to configure environment variables:
    source bigdata_env
  3. If the cluster is in security mode, run the following command to get authenticated:
    kinit Component service user
  4. Run the following commands to create the temporary file ./tmp, obtain spark-archive-2x.zip from HDFS, and decompress it to the tmp directory:
    mkdir tmp
    
    hdfs dfs -get /user/spark2x/jars/8.1.2.2/spark-archive-2x.zip ./
    
    unzip spark-archive-2x.zip -d ./tmp
  5. Switch to the tmp directory, delete the gsjdbc4-VXXXRXXXCXXSPCXXX.jar file, upload the MPPDB Driver file gsjdbc4.jar to the tmp directory, and run the following command to compress the file again:
    zip -r spark-archive-2x.zip *.jar
  6. Delete spark-archive-2x.zip from the HDFS and update the spark-archive-2x.zip package generated in 3.e to the /user/spark2x/jars/8.1.2.2/ directory in the HDFS.
    hdfs dfs -rm /user/spark2x/jars/8.1.2.2/spark-archive-2x.zip
    
    hdfs dfs -put ./spark-archive-2x.zip /user/spark2x/jars/8.1.2.2
4. Restart the Spark service. After the Spark service is restarted, restart the Spark client.

Enable the optimization function.

For all modules that support query pushdown, you can run the SET command on the spark-beeline client to enable the cross-source query optimization function. By default, the function is disabled.

Pushdown configurations can be performed in dimensions of global, data sources, and tables. Commands are as follows:

Global (valid for all data sources):
SET spark.sql.datasource.jdbc = project,aggregate,orderby-limit
Data sources:
SET spark.sql.datasource.${url} = project,aggregate,orderby-limit
Tables:
SET spark.sql.datasource.${url}.${table} = project,aggregate,orderby-limit

When you run the SET command to configure preceding parameters, you are allowed to specify multiple pushdown modules and separate them by commas. The following table lists parameters of corresponding pushdown modules.

**Table 1** Parameters of modules
Module	Parameter Value in the SET Command
project	project
aggregate	aggregate
order by, limit over project or aggregate	orderby-limit

The following is a statement for creating an external table of MySQL:

create table if not exists pdmysql using org.apache.spark.sql.jdbc options(driver "com.mysql.jdbc.Driver", url "jdbc:mysql://ip2:3306/test", user "hive", password "123456", dbtable "mysqldata");

In the preceding statement:

${url} = jdbc:mysql://ip2:3306/test
${table} = mysqldata

NOTE:

On the right of the equal sign (=) is the operators (separated by commas) to be enabled by pushdown.
Priority: table > data source > global. If the table switch is set, the global switch of the data source is invalid for the table. If a data source switch is set, the global switch is invalid for the data source.
The equal sign (=) is not allowed in URL. Equal signs (=) are automatically deleted in the SET clause.
After multiple SET operations, results with different keys will not overwrite each other.

Add functions that support query pushdown.
In addition to query pushdown of mathematical, time, and string functions such as abs(), month(), and length(), you can run the SET command to add a data source that supports query pushdown. Run the following command on the Spark-beeline client:

SET park.sql.datasource.${datasource}.functions = fun1,fun2
Reset the configuration set by the SET command.
Currently, you can only run the RESET command on the spark-beeline client to cancel all SET content. After running the RESET command, all values in the SET command will be cleared. Exercise caution when performing this operation.

The SET command is valid in the current session on the client. After the client is shut down, the content in the SET command turns invalid.

Alternatively, change the value of spark.sql.locale.support in the spark-defaults.conf file to true.

Precautions

Only MySQL, MPPDB, Hive, oracle, and PostgreSQL data sources are supported.

Parent topic: Spark SQL and DataFrame Tuning

Previous topic: Merging CBO

Next topic: SQL Optimization for Multi-level Nesting and Hybrid Join

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot