...
The derived mysql query can be very complex and in many cases we don’t want to split the data source thus run the complex query multiple times on each split. So if the computation is more then just filter and transform, Hive will not split the query result even if “hive.sql.numPartitions” is more than 1.
Using a Non-default Schema
The notion of schema differs from DBMS to DBMS, such as Oracle, MSSQL, MySQL, and PostgreSQL. Correct usage of the hive.sql.schema table property can prevent problems with client connections to external JDBC tables. For more information, see Hive-25591. To create external tables based on a user-defined schema in a JDBC-compliant database, follow the examples below for respective databases.
MariaDB
| Code Block |
|---|
|
CREATE SCHEMA bob;
CREATE TABLE bob.country
(
id int,
name varchar(20)
);
insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA');
CREATE SCHEMA alice;
CREATE TABLE alice.country
(
id int,
name varchar(20)
);
insert into alice.country
values (4, 'Italy');
insert into alice.country
values (5, 'Greece');
insert into alice.country
values (6, 'China');
insert into alice.country
values (7, 'Japan'); |
MS SQL
| Code Block |
|---|
|
CREATE DATABASE world;
USE world;
CREATE SCHEMA bob;
CREATE TABLE bob.country
(
id int,
name varchar(20)
);
insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA');
CREATE SCHEMA alice;
CREATE TABLE alice.country
(
id int,
name varchar(20)
);
insert into alice.country
values (4, 'Italy');
insert into alice.country
values (5, 'Greece');
insert into alice.country
values (6, 'China');
insert into alice.country
values (7, 'Japan'); |
Create a user and associate them with a default schema. For example:
| Code Block |
|---|
|
CREATE LOGIN greg WITH PASSWORD = 'GregPass123!$';
CREATE USER greg FOR LOGIN greg WITH DEFAULT_SCHEMA=bob; |
Allow the user to connect to the database and run queries. For example:
| Code Block |
|---|
|
GRANT CONNECT, SELECT TO greg; |
Oracle
In Oracle, dividing the tables into different namespaces/schemas is achieved through different users. The CREATE SCHEMA statement exists in Oracle, but has different semantics from those defined by SQL Standard and those adopted in other DBMS.
To create "local" users in Oracle you need to be connected to the Pluggable Database (PDB), not to the Container Database (CDB). The following example was tested in Oracle XE edition, using only PDB XEPDB1.
| Code Block |
|---|
|
ALTER SESSION SET CONTAINER = XEPDB1; |
Create the bob schema/user and give appropriate connections to be able to connect to the database. For example:
| Code Block |
|---|
|
CREATE USER bob IDENTIFIED BY bobpass;
ALTER USER bob QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO bob;
CREATE TABLE bob.country
(
id int,
name varchar(20)
);
insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA'); |
Create the alice schema/user and give appropriate connections to be able to connect to the database. For example:
| Code Block |
|---|
|
CREATE USER alice IDENTIFIED BY alicepass;
ALTER USER alice QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO alice;
CREATE TABLE alice.country
(
id int,
name varchar(20)
);
insert into alice.country
values (4, 'Italy');
insert into alice.country
values (5, 'Greece');
insert into alice.country
values (6, 'China');
insert into alice.country
values (7, 'Japan'); |
Without the SELECT ANY privilege, a user cannot see the tables/views of another user. When a user connects to the database using a specific user and schema it is not possible to refer to tables in another user/schema
-- namespace. You need to grant the SELECT ANY privilege. For example:
| Code Block |
|---|
|
GRANT SELECT ANY TABLE TO bob;
GRANT SELECT ANY TABLE TO alice; |
Allow the users to perform inserts on any table/view in the database, not only those present on their own schema. For example:
| Code Block |
|---|
|
GRANT INSERT ANY TABLE TO bob;
GRANT INSERT ANY TABLE TO alice; |
PostgreSQL
| Code Block |
|---|
|
CREATE SCHEMA bob;
CREATE TABLE bob.country
(
id int,
name varchar(20)
);
insert into bob.country
values (1, 'India');
insert into bob.country
values (2, 'Russia');
insert into bob.country
values (3, 'USA');
CREATE SCHEMA alice;
CREATE TABLE alice.country
(
id int,
name varchar(20)
);
insert into alice.country
values (4, 'Italy');
insert into alice.country
values (5, 'Greece');
insert into alice.country
values (6, 'China');
insert into alice.country
values (7, 'Japan'); |
Create a user and associate them with a default schema <=> search_path. For example:
| Code Block |
|---|
|
CREATE ROLE greg WITH LOGIN PASSWORD 'GregPass123!$';
ALTER ROLE greg SET search_path TO bob; |
Grant the necessary permissions to access the schema. For example:
| Code Block |
|---|
|
GRANT USAGE ON SCHEMA bob TO greg;
GRANT SELECT ON ALL TABLES IN SCHEMA bob TO greg; |