You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by wz...@apache.org on 2023/12/02 06:22:13 UTC
(impala) branch master updated: IMPALA-12471: Add unit tests of external jdbc tables for MySQL
This is an automated email from the ASF dual-hosted git repository.
wzhou pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
The following commit(s) were added to refs/heads/master by this push:
new 4c762725c IMPALA-12471: Add unit tests of external jdbc tables for MySQL
4c762725c is described below
commit 4c762725c707f8d150fe250c03faf486008702d4
Author: gaurav1086 <ga...@gmail.com>
AuthorDate: Mon Nov 6 16:42:05 2023 -0800
IMPALA-12471: Add unit tests of external jdbc
tables for MySQL
This patch adds MySql tests for the "external data source"
mechanism in Impala to implement data source for querying JDBC.
This patch also fixes the handling of case-sensitive table and
column names for MySQL query.
Testing:
- Added unit test for mysql and ran unit-test with JDBC
driver mysql-connector-j-8.1.0.jar. This test requires
to add the docker to sudoer's group. Also, the test is
only run in 'exhaustive' mode.
Change-Id: I446ec3d4ebaf53c8edac0b2d181514bde587dfae
Reviewed-on: http://gerrit.cloudera.org:8080/20710
Tested-by: Impala Public Jenkins <im...@cloudera.com>
Reviewed-by: Wenzhe Zhou <wz...@cloudera.com>
---
.../impala/extdatasource/jdbc/JdbcDataSource.java | 16 +-
.../extdatasource/jdbc/dao/DatabaseAccessor.java | 2 +
.../jdbc/dao/GenericJdbcDatabaseAccessor.java | 5 +
.../jdbc/dao/PostgresDatabaseAccessor.java | 12 ++
testdata/bin/clean-mysql-env.sh | 47 +++++
testdata/bin/load-ext-data-sources.sh | 1 -
testdata/bin/setup-mysql-env.sh | 148 ++++++++++++++++
.../queries/QueryTest/mysql-ext-jdbc-tables.test | 195 +++++++++++++++++++++
tests/custom_cluster/test_ext_data_sources.py | 50 ++++++
9 files changed, 463 insertions(+), 13 deletions(-)
diff --git a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/JdbcDataSource.java b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/JdbcDataSource.java
index 28ba111fb..173753d64 100644
--- a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/JdbcDataSource.java
+++ b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/JdbcDataSource.java
@@ -154,6 +154,7 @@ public class JdbcDataSource implements ExternalDataSource {
}
// 2. Build the query and execute it
try {
+ Preconditions.checkState(tableConfig_ != null);
dbAccessor_ = DatabaseAccessorFactory.getAccessor(tableConfig_);
buildQueryAndExecute(params);
} catch (JdbcDatabaseAccessException e) {
@@ -282,11 +283,8 @@ public class JdbcDataSource implements ExternalDataSource {
sb.append(" FROM ");
// Make jdbc table name to be quoted with double quotes if columnMapping is not empty
String jdbcTableName = tableConfig_.get(JdbcStorageConfig.TABLE.getPropertyName());
- if (!columnMapping.isEmpty() && jdbcTableName.charAt(0) != '\"') {
- StringBuilder sb2 = new StringBuilder("\"");
- sb2.append(jdbcTableName);
- sb2.append("\"");
- jdbcTableName = sb2.toString();
+ if (!columnMapping.isEmpty()) {
+ jdbcTableName = dbAccessor_.getCaseSensitiveName(jdbcTableName);
}
sb.append(jdbcTableName);
String condition = QueryConditionUtil
@@ -320,13 +318,7 @@ public class JdbcDataSource implements ExternalDataSource {
for (String mapPair : mappingPairs) {
String[] columns = mapPair.split("=");
// Make jdbc column name to be quoted with double quotes
- String jdbcColumnName = columns[1].trim();
- if (!jdbcColumnName.isEmpty() && jdbcColumnName.charAt(0) != '\"') {
- StringBuilder sb = new StringBuilder("\"");
- sb.append(jdbcColumnName);
- sb.append("\"");
- jdbcColumnName = sb.toString();
- }
+ String jdbcColumnName = dbAccessor_.getCaseSensitiveName(columns[1].trim());
columnMap.put(columns[0].trim(), jdbcColumnName);
}
diff --git a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/DatabaseAccessor.java b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/DatabaseAccessor.java
index 597ae9984..6eab838fc 100644
--- a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/DatabaseAccessor.java
+++ b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/DatabaseAccessor.java
@@ -30,4 +30,6 @@ public interface DatabaseAccessor {
throws JdbcDatabaseAccessException;
void close(boolean cleanCache);
+
+ String getCaseSensitiveName(String name);
}
diff --git a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/GenericJdbcDatabaseAccessor.java b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/GenericJdbcDatabaseAccessor.java
index 1da58fbbe..b2e820c30 100644
--- a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/GenericJdbcDatabaseAccessor.java
+++ b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/GenericJdbcDatabaseAccessor.java
@@ -167,6 +167,11 @@ public class GenericJdbcDatabaseAccessor implements DatabaseAccessor {
}
}
+ @Override
+ public String getCaseSensitiveName(String name) {
+ return name;
+ }
+
/**
* Uses generic JDBC escape functions to add a limit and offset clause to a query
* string
diff --git a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/PostgresDatabaseAccessor.java b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/PostgresDatabaseAccessor.java
index 9ec0d7ab6..247acc742 100644
--- a/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/PostgresDatabaseAccessor.java
+++ b/java/ext-data-source/jdbc/src/main/java/org/apache/impala/extdatasource/jdbc/dao/PostgresDatabaseAccessor.java
@@ -23,6 +23,18 @@ package org.apache.impala.extdatasource.jdbc.dao;
*/
public class PostgresDatabaseAccessor extends GenericJdbcDatabaseAccessor {
+ @Override
+ public String getCaseSensitiveName(String name) {
+ if (!name.isEmpty() && name.charAt(0) != '\"') {
+ StringBuilder sb = new StringBuilder("\"");
+ sb.append(name);
+ sb.append("\"");
+ return sb.toString();
+ } else {
+ return name;
+ }
+ }
+
@Override
protected String addLimitAndOffsetToQuery(String sql, int limit, int offset) {
if (offset == 0) {
diff --git a/testdata/bin/clean-mysql-env.sh b/testdata/bin/clean-mysql-env.sh
new file mode 100755
index 000000000..c9fb5be90
--- /dev/null
+++ b/testdata/bin/clean-mysql-env.sh
@@ -0,0 +1,47 @@
+#!/bin/bash
+#
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+# This script deletes the mysql jdbc, sql files and stops
+# the mysqld container.
+
+set -euo pipefail
+. $IMPALA_HOME/bin/report_build_error.sh
+setup_report_build_error
+
+. ${IMPALA_HOME}/bin/impala-config.sh > /dev/null 2>&1
+
+# Cleanup the jar
+rm -rf /tmp/mysql-connector-j-8*
+
+# Clean up mysql source jar
+rm -rf /tmp/mysql-8.2.0.*
+
+# Clean tmp files
+rm -f /tmp/mysql_jdbc_alltypes.*
+rm -f /tmp/mysql_jdbc.*sql
+
+EXT_DATA_SOURCES_HDFS_PATH=${FILESYSTEM_PREFIX}/test-warehouse/data-sources
+JDBC_DRIVERS_HDFS_PATH=${EXT_DATA_SOURCES_HDFS_PATH}/jdbc-drivers
+
+# Remove jar file of mysql jdbc driver from Hadoop FS.
+hadoop fs -rm -f ${JDBC_DRIVERS_HDFS_PATH}/mysql-jdbc.jar
+echo "Removed mysql-jdbc.jar from HDFS" ${JDBC_DRIVERS_HDFS_PATH}
+
+# Stop the mysqld docker container
+docker stop mysql
diff --git a/testdata/bin/load-ext-data-sources.sh b/testdata/bin/load-ext-data-sources.sh
index 0476bdae2..6d8541fdf 100755
--- a/testdata/bin/load-ext-data-sources.sh
+++ b/testdata/bin/load-ext-data-sources.sh
@@ -77,7 +77,6 @@ sudo -u postgres psql -d functional -c "$loadCmd"
loadCmd="COPY \"AllTypesWithQuote\" FROM '/tmp/jdbc_alltypes.csv' DELIMITER ',' CSV"
sudo -u postgres psql -d functional -c "$loadCmd"
-
# Clean tmp files
rm /tmp/jdbc_alltypes.*
rm /tmp/jdbc_alltypes_with_quote.*
diff --git a/testdata/bin/setup-mysql-env.sh b/testdata/bin/setup-mysql-env.sh
new file mode 100755
index 000000000..0ab6138ed
--- /dev/null
+++ b/testdata/bin/setup-mysql-env.sh
@@ -0,0 +1,148 @@
+#!/bin/bash
+#
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+# This script does the following:
+# 1. Starts the mysqld container and creates databases, user, tables.
+# 2. Loads the test data in the mysql tables from data files.
+# 3. Downloads and installs the mysql jdbc drivers.
+
+set -euo pipefail
+. $IMPALA_HOME/bin/report_build_error.sh
+setup_report_build_error
+
+. ${IMPALA_HOME}/bin/impala-config.sh > /dev/null 2>&1
+
+# Start mysql server in a docker image
+mysqld_status="stopped"
+retry_count=0
+# Check docker command
+if [ $(docker ps > /dev/null 2>&1; echo $?) -gt 0 ];then
+ echo "Error: Can't run docker without sudo"
+ exit 10
+fi
+while [ $retry_count -lt 10 ] && [ $mysqld_status = "stopped" ];
+do
+ docker rm -f mysql 2>/dev/null
+ docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret -d -p 3306:3306 mysql
+ if [ $(docker ps | grep -c mysql) -eq 1 ];then
+ mysqld_status="running"
+ fi
+ # wait 10 seconds before re-trying.
+ sleep 10;
+ ((retry_count+=1))
+done
+
+if [ $mysqld_status = "stopped" ];then
+ echo "Error: Could't start mysqld docker container. Exiting"
+ exit 2
+fi
+
+# Add permission to mysql socket file
+docker exec -i mysql chmod 777 /var/run/mysqld/mysqld.sock
+
+# Run a test query
+if [[ $(docker exec -i mysql mysql -uroot -psecret <<< 'select 1' > \
+/dev/null 2>&1; echo $?) -gt 0 ]]; then
+ echo "Error: Can't run mysql command"
+ exit 20
+fi
+
+# Create database functional and user hiveuser with read/write privileges
+docker exec -i mysql mysql -uroot -psecret <<< 'drop database \
+if exists functional;\
+CREATE DATABASE functional;\
+SET GLOBAL local_infile=1;\
+DROP USER IF EXISTS hiveuser;
+CREATE USER "hiveuser" IDENTIFIED BY "password";\
+GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON functional.* \
+TO "hiveuser";'
+
+# Create jdbc tables
+cat > /tmp/mysql_jdbc_alltypes.sql <<__EOT__
+DROP TABLE IF EXISTS alltypes;
+CREATE TABLE alltypes
+(
+ id INT,
+ bool_col BOOLEAN,
+ tinyint_col SMALLINT,
+ smallint_col SMALLINT,
+ int_col INT,
+ bigint_col BIGINT,
+ float_col FLOAT,
+ double_col DOUBLE PRECISION,
+ date_string_col VARCHAR(8),
+ string_col VARCHAR(10),
+ timestamp_col TIMESTAMP
+);
+__EOT__
+
+docker exec -i mysql mysql -uroot -psecret functional < \
+ /tmp/mysql_jdbc_alltypes.sql
+
+cat > /tmp/mysql_jdbc_alltypes_with_case_sensitive_names.sql <<__EOT__
+DROP TABLE IF EXISTS AllTypesCaseSensitiveNames;
+CREATE TABLE AllTypesCaseSensitiveNames
+(
+ id INT,
+ Bool_col BOOLEAN,
+ Tinyint_col SMALLINT,
+ Smallint_col SMALLINT,
+ Int_col INT,
+ Bigint_col BIGINT,
+ Float_col FLOAT,
+ Double_col DOUBLE PRECISION,
+ Date_string_col VARCHAR(8),
+ String_col VARCHAR(10),
+ Timestamp_col TIMESTAMP
+);
+__EOT__
+
+docker exec -i mysql mysql -uroot -psecret functional < \
+ /tmp/mysql_jdbc_alltypes_with_case_sensitive_names.sql
+
+# Load data to jdbc table
+cat ${IMPALA_HOME}/testdata/target/AllTypes/* > /tmp/mysql_jdbc_alltypes.csv
+docker cp /tmp/mysql_jdbc_alltypes.csv mysql:/tmp
+
+loadCmd="LOAD DATA LOCAL INFILE '/tmp/mysql_jdbc_alltypes.csv' INTO TABLE alltypes \
+ COLUMNS TERMINATED BY ','"
+docker exec -i mysql mysql -uroot -psecret functional --local-infile=1 <<< "$loadCmd"
+
+loadCmd="LOAD DATA LOCAL INFILE '/tmp/mysql_jdbc_alltypes.csv' INTO TABLE \
+ AllTypesCaseSensitiveNames COLUMNS TERMINATED BY ','"
+docker exec -i mysql mysql -uroot -psecret functional --local-infile=1 <<< "$loadCmd"
+
+EXT_DATA_SOURCE_SRC_PATH=${IMPALA_HOME}/java/ext-data-source
+EXT_DATA_SOURCES_HDFS_PATH=${FILESYSTEM_PREFIX}/test-warehouse/data-sources
+JDBC_DRIVERS_HDFS_PATH=${EXT_DATA_SOURCES_HDFS_PATH}/jdbc-drivers
+
+hadoop fs -mkdir -p ${JDBC_DRIVERS_HDFS_PATH}
+
+# Download and Copy mysql JDBC driver to HDFS
+pushd /tmp
+wget "https://downloads.mysql.com/archives\
+/get/p/3/file/mysql-connector-j-8.1.0.tar.gz"
+tar xzf mysql-connector-j-8.1.0.tar.gz
+popd
+hadoop fs -put -f \
+ /tmp/mysql-connector-j-8.1.0/mysql-connector-j-8.1.0.jar \
+ ${JDBC_DRIVERS_HDFS_PATH}/mysql-jdbc.jar
+
+echo "Copied /tmp/mysql-connector-j-8.1.0/mysql-connector-j-8.1.0.jar "\
+ "into HDFS ${JDBC_DRIVERS_HDFS_PATH}"
diff --git a/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test b/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test
new file mode 100644
index 000000000..ff0745242
--- /dev/null
+++ b/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test
@@ -0,0 +1,195 @@
+====
+---- QUERY
+# Create DataSource
+DROP DATA SOURCE IF EXISTS TestJdbcDataSource;
+CREATE DATA SOURCE TestJdbcDataSource
+LOCATION '$FILESYSTEM_PREFIX/test-warehouse/data-sources/jdbc-data-source.jar'
+CLASS 'org.apache.impala.extdatasource.jdbc.JdbcDataSource'
+API_VERSION 'V1';
+---- RESULTS
+'Data source has been created.'
+====
+---- QUERY
+# Show created DataSource
+SHOW DATA SOURCES LIKE 'testjdbcdatasource';
+---- LABELS
+NAME,LOCATION,CLASS NAME,API VERSION
+---- RESULTS
+'testjdbcdatasource',regex:'.*/test-warehouse/data-sources/jdbc-data-source.jar','org.apache.impala.extdatasource.jdbc.JdbcDataSource','V1'
+---- TYPES
+STRING,STRING,STRING,STRING
+====
+---- QUERY
+# Create external JDBC DataSource table
+DROP TABLE IF EXISTS alltypes_jdbc_datasource;
+CREATE TABLE alltypes_jdbc_datasource (
+ id INT,
+ bool_col BOOLEAN,
+ tinyint_col TINYINT,
+ smallint_col SMALLINT,
+ int_col INT,
+ bigint_col BIGINT,
+ float_col FLOAT,
+ double_col DOUBLE,
+ date_string_col STRING,
+ string_col STRING,
+ timestamp_col TIMESTAMP)
+PRODUCED BY DATA SOURCE TestJdbcDataSource(
+'{"database.type":"MYSQL",
+"jdbc.url":"jdbc:mysql://localhost:3306/functional",
+"jdbc.driver":"com.mysql.cj.jdbc.Driver",
+"driver.url":"$FILESYSTEM_PREFIX/test-warehouse/data-sources/jdbc-drivers/mysql-jdbc.jar",
+"dbcp.username":"hiveuser",
+"dbcp.password":"password",
+"table":"alltypes"}');
+---- RESULTS
+'Table has been created.'
+====
+---- QUERY
+# Create external JDBC DataSource table
+DROP TABLE IF EXISTS alltypes_jdbc_datasource_2;
+CREATE TABLE alltypes_jdbc_datasource_2 (
+ id INT,
+ bool_col BOOLEAN,
+ tinyint_col TINYINT,
+ smallint_col SMALLINT,
+ int_col INT,
+ bigint_col BIGINT,
+ float_col FLOAT,
+ double_col DOUBLE,
+ date_string_col STRING,
+ string_col STRING,
+ timestamp_col TIMESTAMP)
+PRODUCED BY DATA SOURCE TestJdbcDataSource(
+'{"database.type":"MYSQL",
+"jdbc.url":"jdbc:mysql://localhost:3306/functional",
+"jdbc.driver":"com.mysql.cj.jdbc.Driver",
+"driver.url":"$FILESYSTEM_PREFIX/test-warehouse/data-sources/jdbc-drivers/mysql-jdbc.jar",
+"dbcp.username":"hiveuser",
+"dbcp.password":"password",
+"table":"AllTypesCaseSensitiveNames",
+"column.mapping":"id=id, bool_col=Bool_col, tinyint_col=Tinyint_col, smallint_col=Smallint_col, int_col=Int_col, bigint_col=Bigint_col, float_col=Float_col, double_col=Double_col, date_string_col=Date_string_col, string_col=String_col, timestamp=Timestamp"}');
+---- RESULTS
+'Table has been created.'
+====
+---- QUERY
+# Test the jdbc DataSource
+# count(*) with a predicate evaluated by Impala
+select count(*) from alltypes_jdbc_datasource
+where float_col = 0 and string_col is not NULL
+---- RESULTS
+730
+---- TYPES
+BIGINT
+====
+---- QUERY
+# count(*) with no predicates has no materialized slots
+select count(*) from alltypes_jdbc_datasource
+---- RESULTS
+7300
+---- TYPES
+BIGINT
+====
+---- QUERY
+# Gets all types including a row with a NULL value. The predicate pushed to
+# the DataSource.
+select *
+from alltypes_jdbc_datasource
+where id > 10 and int_col< 5 limit 5
+---- RESULTS
+11,false,1,1,1,10,1.100000023841858,10.1,'01/02/09','1',2009-01-02 00:11:00
+12,false,2,2,2,20,2.200000047683716,20.2,'01/02/09','2',2009-01-02 00:12:00
+13,false,3,3,3,30,3.299999952316284,30.3,'01/02/09','3',2009-01-02 00:13:00
+14,false,4,4,4,40,4.400000095367432,40.4,'01/02/09','4',2009-01-02 00:14:01
+20,false,0,0,0,0,0,0,'01/03/09','0',2009-01-03 00:20:01
+---- TYPES
+INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP
+====
+---- QUERY
+# Gets specified columns.
+select id, bool_col, smallint_col, float_col, double_col, date_string_col
+from alltypes_jdbc_datasource
+where id > 10 and int_col< 5 limit 5
+---- RESULTS
+11,false,1,1.100000023841858,10.1,'01/02/09'
+12,false,2,2.200000047683716,20.2,'01/02/09'
+13,false,3,3.299999952316284,30.3,'01/02/09'
+14,false,4,4.400000095367432,40.4,'01/02/09'
+20,false,0,0,0,'01/03/09'
+---- TYPES
+INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, STRING
+====
+---- QUERY
+# Gets specified columns from external jdbc table with case sensitive column names
+# and table name.
+select id, bool_col, smallint_col, float_col, double_col, date_string_col
+from alltypes_jdbc_datasource_2
+where id > 10 and int_col< 5 limit 5
+---- RESULTS
+11,false,1,1.100000023841858,10.1,'01/02/09'
+12,false,2,2.200000047683716,20.2,'01/02/09'
+13,false,3,3.299999952316284,30.3,'01/02/09'
+14,false,4,4.400000095367432,40.4,'01/02/09'
+20,false,0,0,0,'01/03/09'
+---- TYPES
+INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, STRING
+====
+---- QUERY
+# Inner join with a non jdbc table
+select a.id, b.int_col
+from alltypes_jdbc_datasource a inner join functional.alltypes b on (a.id = b.id)
+where a.id = 1
+---- RESULTS
+1,1
+---- TYPES
+INT, INT
+====
+---- QUERY
+# Inner join with another jdbc table
+select a.id, b.int_col
+from alltypes_jdbc_datasource a inner join alltypes_jdbc_datasource_2 b on (a.id = b.id)
+where a.id < 3 group by a.id, b.int_col
+---- RESULTS
+0,0
+1,1
+2,2
+---- TYPES
+INT, INT
+====
+---- QUERY
+# Cross join
+select a.id, b.id
+from alltypes_jdbc_datasource a cross join alltypes_jdbc_datasource b
+where (a.id < 3 and b.id < 3)
+order by a.id, b.id limit 10
+---- RESULTS
+0,0
+0,1
+0,2
+1,0
+1,1
+1,2
+2,0
+2,1
+2,2
+---- TYPES
+INT, INT
+====
+---- QUERY
+# Drop table
+DROP TABLE alltypes_jdbc_datasource;
+---- RESULTS
+'Table has been dropped.'
+====
+---- QUERY
+# Drop table
+DROP TABLE alltypes_jdbc_datasource_2;
+---- RESULTS
+'Table has been dropped.'
+====
+---- QUERY
+# Drop DataSource
+DROP DATA SOURCE TestJdbcDataSource;
+---- RESULTS
+'Data source has been dropped.'
+====
diff --git a/tests/custom_cluster/test_ext_data_sources.py b/tests/custom_cluster/test_ext_data_sources.py
index 850e87e19..a80b0dbf3 100644
--- a/tests/custom_cluster/test_ext_data_sources.py
+++ b/tests/custom_cluster/test_ext_data_sources.py
@@ -17,6 +17,8 @@
from __future__ import absolute_import, division, print_function
import pytest
+import os
+import subprocess
from tests.common.custom_cluster_test_suite import CustomClusterTestSuite
@@ -57,3 +59,51 @@ class TestExtDataSources(CustomClusterTestSuite):
def test_data_source_small_batch_size(self, vector, unique_database):
"""Run test with batch size less than default size 1024"""
self.run_test_case('QueryTest/data-source-tables', vector, use_db=unique_database)
+
+
+class TestMySqlExtJdbcTables(CustomClusterTestSuite):
+ """Impala query tests for external jdbc tables on MySQL server."""
+
+ @classmethod
+ def get_workload(cls):
+ return 'functional-query'
+
+ @classmethod
+ def _setup_mysql_test_env(cls):
+ # Download MySQL docker image and jdbc driver, start MySQL server, create database
+ # and tables, create user account, load testing data, copy jdbc driver to HDFS, etc.
+ script = os.path.join(os.environ['IMPALA_HOME'], 'testdata/bin/setup-mysql-env.sh')
+ run_cmd = [script]
+ try:
+ subprocess.check_call(run_cmd, close_fds=True)
+ except subprocess.CalledProcessError as e:
+ if e.returncode == 10:
+ pytest.skip("These tests requireadd the docker to be added to sudoer's group")
+ elif e.returncode == 20:
+ pytest.skip("Can't connect to local MySQL server")
+ else:
+ assert False, "Failed to setup MySQL testing environment"
+
+ @classmethod
+ def _remove_mysql_test_env(cls):
+ # Tear down MySQL server, remove its docker image, etc.
+ script = os.path.join(os.environ['IMPALA_HOME'], 'testdata/bin/clean-mysql-env.sh')
+ run_cmd = [script]
+ subprocess.check_call(run_cmd, close_fds=True)
+
+ @classmethod
+ def setup_class(cls):
+ if cls.exploration_strategy() != 'exhaustive':
+ pytest.skip('These tests only run in exhaustive')
+ cls._setup_mysql_test_env()
+ super(TestMySqlExtJdbcTables, cls).setup_class()
+
+ @classmethod
+ def teardown_class(cls):
+ cls._remove_mysql_test_env()
+ super(TestMySqlExtJdbcTables, cls).teardown_class()
+
+ @pytest.mark.execute_serially
+ def test_mysql_ext_jdbc_tables(self, vector, unique_database):
+ """Run tests for external jdbc tables on MySQL"""
+ self.run_test_case('QueryTest/mysql-ext-jdbc-tables', vector, use_db=unique_database)