[
https://issues.apache.org/jira/browse/HIVE-1555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15681496#comment-15681496
]
Dmitry Zagorulkin edited comment on HIVE-1555 at 11/20/16 5:23 PM:
-------------------------------------------------------------------
[~xuefuz] I've implemented simple solution with some hard code by one.
It's tested with oracle database.
{code:sql}
beeline> !connect jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000:
Enter password for jdbc:hive2://localhost:10000:
Connected to: Apache Hive (version 2.2.0-SNAPSHOT)
Driver: Hive JDBC (version 2.2.0-SNAPSHOT)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> SET
hive.metastore.warehouse.dir=${env:HOME}/Documents/hive-warehouse;
No rows affected (0.158 seconds)
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> CREATE EXTERNAL TABLE books3 (
. . . . . . . . . . . . . . . .> book_id INT,
. . . . . . . . . . . . . . . .> book_name STRING,
. . . . . . . . . . . . . . . .> author_name STRING,
. . . . . . . . . . . . . . . .> book_isbn STRING
. . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . .> STORED BY
"org.apache.hive.storagehandler.JDBCStorageHandler"
. . . . . . . . . . . . . . . .> TBLPROPERTIES (
. . . . . . . . . . . . . . . .> "mapred.jdbc.driver.class" =
"oracle.jdbc.OracleDriver",
. . . . . . . . . . . . . . . .> "mapred.jdbc.url" =
"jdbc:oracle:thin:@//localhost:49161/XE",
. . . . . . . . . . . . . . . .> "mapred.jdbc.username" = "*",
. . . . . . . . . . . . . . . .> "mapred.jdbc.password" = "*",
. . . . . . . . . . . . . . . .> "hive.jdbc.update.on.duplicate" = "true",
. . . . . . . . . . . . . . . .> "mapreduce.jdbc.input.table.name" = "books"
. . . . . . . . . . . . . . . .> );
No rows affected (2.297 seconds)
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> select * from books3;
+-----------------+-------------------+---------------------+-------------------+
| books3.book_id | books3.book_name | books3.author_name | books3.book_isbn
|
+-----------------+-------------------+---------------------+-------------------+
| 124123 | name | author | 132321adsaf31
|
| 13 | name2 | author2 | asd213fadsf
|
| 2345236 | name3 | author3 | asdfds1234123
|
+-----------------+-------------------+---------------------+-------------------+
3 rows selected (2.146 seconds)
0: jdbc:hive2://localhost:10000> explain select * from books3;
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: books3 |
| Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column
stats: NONE |
| Select Operator |
| expressions: book_id (type: string), book_name (type: string),
author_name (type: string), book_isbn (type: string) |
| outputColumnNames: _col0, _col1, _col2, _col3 |
| Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column
stats: NONE |
| ListSink |
| |
+----------------------------------------------------+
17 rows selected (0.508 seconds)
{code}
*What do you think about add information about external table inside serde
properties section?*
was (Author: hddimon):
[~xuefuz] I've implemented simple solution with some hard code by one.
It's tested with oracle database.
beeline> !connect jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000:
Enter password for jdbc:hive2://localhost:10000:
Connected to: Apache Hive (version 2.2.0-SNAPSHOT)
Driver: Hive JDBC (version 2.2.0-SNAPSHOT)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> SET
hive.metastore.warehouse.dir=${env:HOME}/Documents/hive-warehouse;
No rows affected (0.158 seconds)
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> CREATE EXTERNAL TABLE books3 (
. . . . . . . . . . . . . . . .> book_id INT,
. . . . . . . . . . . . . . . .> book_name STRING,
. . . . . . . . . . . . . . . .> author_name STRING,
. . . . . . . . . . . . . . . .> book_isbn STRING
. . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . .> STORED BY
"org.apache.hive.storagehandler.JDBCStorageHandler"
. . . . . . . . . . . . . . . .> TBLPROPERTIES (
. . . . . . . . . . . . . . . .> "mapred.jdbc.driver.class" =
"oracle.jdbc.OracleDriver",
. . . . . . . . . . . . . . . .> "mapred.jdbc.url" =
"jdbc:oracle:thin:@//localhost:49161/XE",
. . . . . . . . . . . . . . . .> "mapred.jdbc.username" = "*",
. . . . . . . . . . . . . . . .> "mapred.jdbc.password" = "*",
. . . . . . . . . . . . . . . .> "hive.jdbc.update.on.duplicate" = "true",
. . . . . . . . . . . . . . . .> "mapreduce.jdbc.input.table.name" = "books"
. . . . . . . . . . . . . . . .> );
No rows affected (2.297 seconds)
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> select * from books3;
+-----------------+-------------------+---------------------+-------------------+
| books3.book_id | books3.book_name | books3.author_name | books3.book_isbn
|
+-----------------+-------------------+---------------------+-------------------+
| 124123 | name | author | 132321adsaf31
|
| 13 | name2 | author2 | asd213fadsf
|
| 2345236 | name3 | author3 | asdfds1234123
|
+-----------------+-------------------+---------------------+-------------------+
3 rows selected (2.146 seconds)
0: jdbc:hive2://localhost:10000> explain select * from books3;
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: books3 |
| Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column
stats: NONE |
| Select Operator |
| expressions: book_id (type: string), book_name (type: string),
author_name (type: string), book_isbn (type: string) |
| outputColumnNames: _col0, _col1, _col2, _col3 |
| Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column
stats: NONE |
| ListSink |
| |
+----------------------------------------------------+
17 rows selected (0.508 seconds)
*What do you think about add information about external table inside serde
properties section?*
> JDBC Storage Handler
> --------------------
>
> Key: HIVE-1555
> URL: https://issues.apache.org/jira/browse/HIVE-1555
> Project: Hive
> Issue Type: New Feature
> Components: JDBC
> Reporter: Bob Robertson
> Assignee: Dmitry Zagorulkin
> Attachments: JDBCStorageHandler Design Doc.pdf
>
> Original Estimate: 24h
> Remaining Estimate: 24h
>
> With the Cassandra and HBase Storage Handlers I thought it would make sense
> to include a generic JDBC RDBMS Storage Handler so that you could import a
> standard DB table into Hive. Many people must want to perform HiveQL joins,
> etc against tables in other systems etc.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
