====
---- 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_col DATE,
 string_col STRING,
 timestamp_col TIMESTAMP)
STORED BY JDBC
TBLPROPERTIES (
"database.type"="POSTGRES",
"jdbc.url"="jdbc:postgresql://$INTERNAL_LISTEN_HOST:5432/functional",
"jdbc.properties"="connect_timeout=20, application_name=\"myapp\"",
"jdbc.driver"="org.postgresql.Driver",
"driver.url"="$FILESYSTEM_PREFIX/test-warehouse/data-sources/jdbc-drivers/postgresql-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_col DATE,
 string_col STRING,
 timestamp_col TIMESTAMP)
STORED BY JDBC
TBLPROPERTIES (
"database.type"="POSTGRES",
"jdbc.url"="jdbc:postgresql://$INTERNAL_LISTEN_HOST:5432/functional",
"jdbc.driver"="org.postgresql.Driver",
"driver.url"="$FILESYSTEM_PREFIX/test-warehouse/data-sources/jdbc-drivers/postgresql-jdbc.jar",
"dbcp.username"="hiveuser",
"dbcp.password"="password",
"table"="AllTypesWithQuote",
"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_col=date_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,2009-01-02,'1',2009-01-02 00:11:00.450000000
12,true,2,2,2,20,2.200000047683716,20.2,2009-01-02,'2',2009-01-02 00:12:00.460000000
13,false,3,3,3,30,3.299999952316284,30.3,2009-01-02,'3',2009-01-02 00:13:00.480000000
14,true,4,4,4,40,4.400000095367432,40.4,2009-01-02,'4',2009-01-02 00:14:00.510000000
20,true,0,0,0,0,0,0,2009-01-03,'0',2009-01-03 00:20:00.900000000
---- TYPES
INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DATE, STRING, TIMESTAMP
====
---- QUERY
# Gets specified columns.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where id > 10 and int_col< 5 limit 5
---- RESULTS
11,false,1,1.100000023841858,10.1,2009-01-02
12,true,2,2.200000047683716,20.2,2009-01-02
13,false,3,3.299999952316284,30.3,2009-01-02
14,true,4,4.400000095367432,40.4,2009-01-02
20,true,0,0,0,2009-01-03
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- 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_col
from alltypes_jdbc_datasource_2
where id > 10 and int_col< 5 limit 5
---- RESULTS
11,false,1,1.100000023841858,10.1,2009-01-02
12,true,2,2.200000047683716,20.2,2009-01-02
13,false,3,3.299999952316284,30.3,2009-01-02
14,true,4,4.400000095367432,40.4,2009-01-02
20,true,0,0,0,2009-01-03
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- 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
# Gets specified columns based on date predicate with operator '='.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col = DATE '2009-01-02' order by id limit 5;
---- RESULTS
10,true,0,0,0,2009-01-02
11,false,1,1.100000023841858,10.1,2009-01-02
12,true,2,2.200000047683716,20.2,2009-01-02
13,false,3,3.299999952316284,30.3,2009-01-02
14,true,4,4.400000095367432,40.4,2009-01-02
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on date predicate with operator '='
# with empty result.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col = DATE '1990-01-01' order by id limit 5;
---- RESULTS
====
---- QUERY
# Gets specified columns based on date predicate with operator '>'.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col > DATE '2009-01-02' order by id limit 5;
---- RESULTS
20,true,0,0,0,2009-01-03
21,false,1,1.100000023841858,10.1,2009-01-03
22,true,2,2.200000047683716,20.2,2009-01-03
23,false,3,3.299999952316284,30.3,2009-01-03
24,true,4,4.400000095367432,40.4,2009-01-03
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on date predicate with operator '>'
# with empty result.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col > DATE '2990-01-01' order by id limit 5;
---- RESULTS
====
---- QUERY
# Gets specified columns based on date predicate with operator '<'.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col < DATE '2009-01-02' order by id limit 5;
---- RESULTS
0,true,0,0,0,2009-01-01
1,false,1,1.100000023841858,10.1,2009-01-01
2,true,2,2.200000047683716,20.2,2009-01-01
3,false,3,3.299999952316284,30.3,2009-01-01
4,true,4,4.400000095367432,40.4,2009-01-01
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on date predicate with operator '<'
# with empty result.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col < DATE '1990-01-01' order by id limit 5;
---- RESULTS
====
---- QUERY
# Gets specified columns based on date predicate with operator '>='.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col >= DATE '2009-01-02' order by id limit 5;
---- RESULTS
10,true,0,0,0,2009-01-02
11,false,1,1.100000023841858,10.1,2009-01-02
12,true,2,2.200000047683716,20.2,2009-01-02
13,false,3,3.299999952316284,30.3,2009-01-02
14,true,4,4.400000095367432,40.4,2009-01-02
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on date predicate with operator '<='.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col <= DATE '2009-01-02' order by id limit 5;
---- RESULTS
0,true,0,0,0,2009-01-01
1,false,1,1.100000023841858,10.1,2009-01-01
2,true,2,2.200000047683716,20.2,2009-01-01
3,false,3,3.299999952316284,30.3,2009-01-01
4,true,4,4.400000095367432,40.4,2009-01-01
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on date predicate with operator '!='.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col != DATE '2009-01-01' order by id limit 5;
---- RESULTS
10,true,0,0,0,2009-01-02
11,false,1,1.100000023841858,10.1,2009-01-02
12,true,2,2.200000047683716,20.2,2009-01-02
13,false,3,3.299999952316284,30.3,2009-01-02
14,true,4,4.400000095367432,40.4,2009-01-02
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on date predicate with operator 'between'.
select id, bool_col, smallint_col, float_col, double_col, date_col
from alltypes_jdbc_datasource
where date_col between DATE '2009-03-01' and DATE '2009-04-01' order by id limit 5;
---- RESULTS
590,true,0,0,0,2009-03-01
591,false,1,1.100000023841858,10.1,2009-03-01
592,true,2,2.200000047683716,20.2,2009-03-01
593,false,3,3.299999952316284,30.3,2009-03-01
594,true,4,4.400000095367432,40.4,2009-03-01
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '='.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col = '2009-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
12,true,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00.460000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '='
# with empty result.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col = '1990-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '>'.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col > '2009-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00.480000000
14,true,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:00.510000000
15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:00.550000000
16,true,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:00.600000000
17,false,7,7.699999809265137,70.7,2009-01-02,2009-01-02 00:17:00.660000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '>'
# with empty result.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col > '2990-01-01 00:12:00.460000000' order by id limit 5;
---- RESULTS
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '<'.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col < '2009-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
0,true,0,0,0,2009-01-01,2009-01-01 00:00:00
1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00
2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000
3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000
4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '<'
# with empty result.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col <  '1990-01-01 00:12:00.460000000' order by id limit 5;
---- RESULTS
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '>='.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col >= '2009-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
12,true,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00.460000000
13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00.480000000
14,true,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:00.510000000
15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:00.550000000
16,true,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:00.600000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '<='.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col <= '2009-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
0,true,0,0,0,2009-01-01,2009-01-01 00:00:00
1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00
2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000
3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000
4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator '!='.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col != '2009-01-02 00:12:00.460000000' order by id limit 5;
---- RESULTS
0,true,0,0,0,2009-01-01,2009-01-01 00:00:00
1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00
2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000
3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000
4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate with operator 'between'.
select count(*) from alltypes_jdbc_datasource where timestamp_col between
'2009-01-03 00:12:00' and '2009-01-05 00:12:00'
---- RESULTS
20
---- TYPES
BIGINT
====
---- QUERY
# Gets specified columns based on timestamp predicate without time in timestamp.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col > '2009-01-03' order by id limit 5;
---- RESULTS
20,true,0,0,0,2009-01-03,2009-01-03 00:20:00.900000000
21,false,1,1.100000023841858,10.1,2009-01-03,2009-01-03 00:21:00.900000000
22,true,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:00.910000000
23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:00.930000000
24,true,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:00.960000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- QUERY
# Gets specified columns based on timestamp predicate without milisecond in timestamp.
select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col
from alltypes_jdbc_datasource
where timestamp_col > '2009-01-03 00:22:00' order by id limit 5;
---- RESULTS
22,true,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:00.910000000
23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:00.930000000
24,true,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:00.960000000
25,false,5,5.5,50.5,2009-01-03,2009-01-03 00:25:01
26,true,6,6.599999904632568,60.59999999999999,2009-01-03,2009-01-03 00:26:01.500000000
---- TYPES
INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP
====
---- 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
# Create JDBC table with unknown database type.
DROP TABLE IF EXISTS jdbc_test;
CREATE TABLE jdbc_test (
 id INT,
 bool_col BOOLEAN,
 int_col INT)
STORED BY JDBC
TBLPROPERTIES (
"database.type"="UNKNOWN",
"jdbc.url"="jdbc:postgresql://$INTERNAL_LISTEN_HOST:5432/functional",
"jdbc.driver"="org.postgresql.Driver",
"driver.url"="$FILESYSTEM_PREFIX/test-warehouse/data-sources/jdbc-drivers/postgresql-jdbc.jar",
"dbcp.username"="hiveuser",
"dbcp.password"="password",
"table"="alltypes");
---- RESULTS
'Table has been created.'
====
---- QUERY
select count(*) from jdbc_test;
---- CATCH
row_regex:.*InternalException:.*Unknown database type.
====
---- QUERY
# Alter JDBC table to change database type as POSTGRES.
ALTER TABLE jdbc_test
SET TBLPROPERTIES ("database.type"="POSTGRES");
---- RESULTS
'Updated table.'
====
---- QUERY
select count(*) from jdbc_test;
---- RESULTS
7300
---- TYPES
BIGINT
====
---- QUERY
# Alter JDBC table to set wrong username and password.
ALTER TABLE jdbc_test
SET TBLPROPERTIES ("dbcp.username"="UNKNOWN", "dbcp.password"="NONE");
---- RESULTS
'Updated table.'
====
---- QUERY
select * from jdbc_test order by id limit 5;
---- CATCH
row_regex:.*(FATAL: password authentication failed for user "UNKNOWN"|FATAL: role "UNKNOWN" does not exist).*
====
---- QUERY
# Alter JDBC table to set correct username and password.
ALTER TABLE jdbc_test
SET TBLPROPERTIES ("dbcp.username"="hiveuser", "dbcp.password"="password");
---- RESULTS
'Updated table.'
====
---- QUERY
select * from jdbc_test order by id limit 5;
---- RESULTS
0,true,0
1,false,1
2,true,2
3,false,3
4,true,4
---- TYPES
INT, BOOLEAN, INT
====
---- QUERY
# Alter JDBC table to unset the required JDBC configuration "table".
ALTER TABLE jdbc_test UNSET TBLPROPERTIES ("table");
---- CATCH
row_regex:.*AnalysisException: Unsetting the 'table' table property is not supported for JDBC DataSource table
====
---- QUERY
# Alter table to drop a column.
ALTER TABLE jdbc_test DROP COLUMN int_col;
---- RESULTS
'Column has been dropped.'
====
---- QUERY
# Alter table to add a new column.
ALTER TABLE jdbc_test ADD COLUMN IF NOT EXISTS date_col DATE;
---- RESULTS
'New column(s) have been added to the table.'
====
---- QUERY
select * from jdbc_test order by id limit 5;
---- RESULTS
0,true,2009-01-01
1,false,2009-01-01
2,true,2009-01-01
3,false,2009-01-01
4,true,2009-01-01
---- TYPES
INT, BOOLEAN, DATE
====
---- QUERY
# Alter table to change a column.
ALTER TABLE jdbc_test CHANGE COLUMN date_col timestamp_col TIMESTAMP;
---- RESULTS
'Column has been altered.'
====
---- QUERY
select * from jdbc_test order by id limit 5;
---- RESULTS
0,true,2009-01-01 00:00:00
1,false,2009-01-01 00:01:00
2,true,2009-01-01 00:02:00.100000000
3,false,2009-01-01 00:03:00.300000000
4,true,2009-01-01 00:04:00.600000000
---- TYPES
INT, BOOLEAN, TIMESTAMP
====
---- QUERY
# Drop table.
DROP TABLE jdbc_test;
---- RESULTS
'Table has been dropped.'
====