--! qt:database:mariadb:q_test_country_table_with_schema.mariadb.sql

-- In MariaDB (and MySQL) CREATE SCHEMA is a synonym to CREATE DATABASE so the use of hive.sql.schema is not required.
-- A MariaDB table can be uniquely identified by including the database/schema name in the JDBC URL and specifying the
-- hive.sql.table property.  

-- Connecting to MariaDB without specifying a database name (e.g., jdbc:mariadb://localhost:3309/) may create problems
-- when a table with the same name exists in multiple databases. The problem could be avoided by setting the
-- hive.sql.schema property but unfortunately the JDBC driver of MariaDB ignores schema information.

-- Some JDBC APIs require the catalog, schema, and table names to be passed exactly as they are stored in the database.
-- MariaDB stores unquoted identifiers by first converting them to lowercase thus the hive.sql.schema and
-- hive.sql.table properties below are specified in lowercase.

-- The hive.sql.schema property is optional; bob schema is inferred from the JDBC URL 
CREATE EXTERNAL TABLE country_0 (id int, name varchar(20))
    STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
    TBLPROPERTIES (
        "hive.sql.database.type" = "MYSQL",
        "hive.sql.jdbc.driver" = "org.mariadb.jdbc.Driver",
        "hive.sql.jdbc.url" = "jdbc:mariadb://localhost:3309/bob",
        "hive.sql.dbcp.username" = "root",
        "hive.sql.dbcp.password" = "qtestpassword",
        "hive.sql.table" = "country");

EXPLAIN CBO SELECT COUNT(*) FROM country_0;
SELECT COUNT(*) FROM country_0;

-- The hive.sql.schema property can be specified with the same value as the database name in the URL but does not
-- provide any additional benefits. 
CREATE EXTERNAL TABLE country_1 (id int, name varchar(20))
    STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
    TBLPROPERTIES (
        "hive.sql.database.type" = "MYSQL",
        "hive.sql.jdbc.driver" = "org.mariadb.jdbc.Driver",
        "hive.sql.jdbc.url" = "jdbc:mariadb://localhost:3309/bob",
        "hive.sql.dbcp.username" = "root",
        "hive.sql.dbcp.password" = "qtestpassword",
        "hive.sql.schema" = "bob",
        "hive.sql.table" = "country");

EXPLAIN CBO SELECT COUNT(*) FROM country_1;
SELECT COUNT(*) FROM country_1;

CREATE EXTERNAL TABLE country_2 (id int, name varchar(20))
    STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
    TBLPROPERTIES (
        "hive.sql.database.type" = "MYSQL",
        "hive.sql.jdbc.driver" = "org.mariadb.jdbc.Driver",
        "hive.sql.jdbc.url" = "jdbc:mariadb://localhost:3309/alice",
        "hive.sql.dbcp.username" = "root",
        "hive.sql.dbcp.password" = "qtestpassword",
        "hive.sql.table" = "country");

EXPLAIN CBO SELECT COUNT(*) FROM country_2;
SELECT COUNT(*) FROM country_2;

-- It is possible to have the JDBC URL and hive.sql.schema pointing to different databases/schemas but it is confusing
-- and leads to the same result which could be achieved by using exclusively the URL.
CREATE EXTERNAL TABLE country_3 (id int, name varchar(20))
    STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
    TBLPROPERTIES (
        "hive.sql.database.type" = "MYSQL",
        "hive.sql.jdbc.driver" = "org.mariadb.jdbc.Driver",
        "hive.sql.jdbc.url" = "jdbc:mariadb://localhost:3309/bob",
        "hive.sql.dbcp.username" = "root",
        "hive.sql.dbcp.password" = "qtestpassword",
        "hive.sql.schema" = "alice",
        "hive.sql.table" = "country");

EXPLAIN CBO SELECT COUNT(*) FROM country_3;
SELECT COUNT(*) FROM country_3;