16.sqoop-create-hive-table
Thecreate-hive-table
tool populates a Hive metastore with a definition for a table based on a database table previously imported to HDFS, or one planned to be imported. This effectively performs the "--hive-import
" step ofsqoop-import
without running the preceeding import.
在create-hive-table工具填充一个元存储通过定义一个表基于一个以前已经导入到HDFS的数据库表,或一个计划被导入。”--hive-import ”是sqoop-import其中的一步,不需要先执行导入。
(create-hive-table 的用途 1 在hive定义一个表名关联到已经导入到HDFS中的表 2 在 hive中创建一个表,这个表可以用于以后的导入)
If data was already loaded to HDFS, you can use this tool to finish the pipeline of importing the data to Hive. You can also create Hive tables with this tool; data then can be imported and populated into the targetafter a preprocessing step run by the user.
如果数据已经加载到HDFS,您可以使用此工具来完成导入数据到hive的管道(关联导入的数据到hive)。您还可以使用此工具创建hive表;然后数据可以导入并填充到目标在用户运行一个预处理步骤后。
$ sqoop create-hive-table (generic-args) (create-hive-table-args) $ sqoop-create-hive-table (generic-args) (create-hive-table-args)
Although the Hadoop generic arguments must preceed any create-hive-table arguments, the create-hive-table arguments can be entered in any order with respect to one another(这个句子翻译好多次了).
Table34.Common arguments
Argument | Description |
---|---|
--connect <jdbc-uri> | Specify JDBC connect string |
--connection-manager <class-name> | Specify connection manager class to use |
--driver <class-name> | Manually specify JDBC driver class to use |
--hadoop-mapred-home <dir> | Override $HADOOP_MAPRED_HOME |
--help | Print usage instructions |
-P | Read password from console |
--password <password> | Set authentication password |
--username <username> | Set authentication username |
--verbose | Print more information while working |
--connection-param-file <filename> | Optional properties file that provides connection parameters |
Table35.Hive arguments:
Argument | Description |
---|---|
--hive-home <dir> | Override$HIVE_HOME |
--hive-overwrite | Overwrite existing data in the Hive table. |
--create-hive-table | If set, then the job will fail if the target hive |
table exits. By default this property is false. | |
--hive-table <table-name> | Sets the table name to use when importing to Hive. |
--table | The database table to read the definition from. |
Table36.Output line formatting arguments:
Argument | Description |
---|---|
--enclosed-by <char> | Sets a required field enclosing character |
--escaped-by <char> | Sets the escape character |
--fields-terminated-by <char> | Sets the field separator character |
--lines-terminated-by <char> | Sets the end-of-line character |
--mysql-delimiters | Uses MySQL’s default delimiter set: fields:, lines:\n escaped-by:\ optionally-enclosed-by:' |
--optionally-enclosed-by <char> | Sets a field enclosing character |
Do not use enclosed-by or escaped-by delimiters with output formatting arguments used to import to Hive. Hive cannot currently parse them.
Define in Hive a table namedemps
with a definition based on a database table namedemployees
:
定义在一个名叫emps蜂巢表与一个基于数据库表定义一个名为员工
在hive中定义名为emps的表 通过一个名为employees的数据库表的定义(建表需要表结构):
$ sqoop create-hive-table --connect jdbc:mysql://db.example.com/corp \ --table employees --hive-table emps
sqoop-eval :返回sql 查询结果,可以检验查询结果是否符自己的预期
Theeval
tool allows users to quickly run simple SQL queries against a database; results are printed to the console. This allows users to preview their import queries to ensure they import the data they expect.
eval工具允许用户快速运行简单的SQL查询数据库;结果打印到控制台。这允许用户预览他们的导入查询以确保他们导入数据他们期望
$ sqoop eval (generic-args) (eval-args) $ sqoop-eval (generic-args) (eval-args)
Although the Hadoop generic arguments must preceed any eval arguments, the eval arguments can be entered in any order with respect to one another.
Table37.Common arguments
Argument | Description |
---|---|
--connect <jdbc-uri> | Specify JDBC connect string |
--connection-manager <class-name> | Specify connection manager class to use |
--driver <class-name> | Manually specify JDBC driver class to use |
--hadoop-mapred-home <dir> | Override $HADOOP_MAPRED_HOME |
--help | Print usage instructions |
-P | Read password from console |
--password <password> | Set authentication password |
--username <username> | Set authentication username |
--verbose | Print more information while working |
--connection-param-file <filename> | Optional properties file that provides connection parameters |
Select ten records from theemployees
table:
从employees表中查询10条记录
$ sqoop eval --connect jdbc:mysql://db.example.com/corp \ --query "SELECT * FROM employees LIMIT 10"
Insert a row into thefoo
table:
插入一行到foo表:
$ sqoop eval --connect jdbc:mysql://db.example.com/corp \ -e "INSERT INTO foo VALUES(42, 'bar')"
$ sqoop list-databases (generic-args) (list-databases-args) $ sqoop-list-databases (generic-args) (list-databases-args)
Although the Hadoop generic arguments must preceed any list-databases arguments, the list-databases arguments can be entered in any order with respect to one another.
Table39.Common arguments
Argument | Description |
---|---|
--connect <jdbc-uri> | Specify JDBC connect string |
--connection-manager <class-name> | Specify connection manager class to use |
--driver <class-name> | Manually specify JDBC driver class to use |
--hadoop-mapred-home <dir> | Override $HADOOP_MAPRED_HOME |
--help | Print usage instructions |
-P | Read password from console |
--password <password> | Set authentication password |
--username <username> | Set authentication username |
--verbose | Print more information while working |
--connection-param-file <filename> | Optional properties file that provides connection parameters |
List database schemas available on a MySQL server:
列出MySQL服务器上可用的数据库的名:
$ sqoop list-databases --connect jdbc:mysql://database.example.com/ information_schema employees
![]() | Note |
---|---|
This only works with HSQLDB, MySQL and Oracle. When using with Oracle, it is necessary that the user connecting to the database has DBA privileges. |
$ sqoop list-tables (generic-args) (list-tables-args) $ sqoop-list-tables (generic-args) (list-tables-args)
Although the Hadoop generic arguments must preceed any list-tables arguments, the list-tables arguments can be entered in any order with respect to one another.
Table40.Common arguments
Argument | Description |
---|---|
--connect <jdbc-uri> | Specify JDBC connect string |
--connection-manager <class-name> | Specify connection manager class to use |
--driver <class-name> | Manually specify JDBC driver class to use |
--hadoop-mapred-home <dir> | Override $HADOOP_MAPRED_HOME |
--help | Print usage instructions |
-P | Read password from console |
--password <password> | Set authentication password |
--username <username> | Set authentication username |
--verbose | Print more information while working |
--connection-param-file <filename> | Optional properties file that provides connection parameters |
List tables available in the "corp" database:
$ sqoop list-tables --connect jdbc:mysql://database.example.com/corp employees payroll_checks job_descriptions office_supplies
In case of postgresql, list tables command with common arguments fetches only "public" schema. For custom schema, use --schema argument to list tables of particular schema Example
如果是postgresql数据库,表列表命令只在“公共”模式下好用。对于自定义模式,使用 --schema来列出表名
$ sqoop list-tables --connect jdbc:postgresql://localhost/corp --username name -P -- --schema payrolldept employees expenses
$ sqoop help [tool-name] $ sqoop-help [tool-name]
If no tool name is provided (for example, the user runssqoop help
), then the available tools are listed. With a tool name, the usage instructions for that specific tool are presented on the console.
如果没有工具的名字被提供(例如,用户运行 sqoop help),这时 所有可用的工具都列出。指定一个工具名称(如:sqoop help import ),这个工具的使用说明显示在控制台。
List available tools:
列出所有有效的工具:
$ sqoop help usage: sqoop COMMAND [ARGS] Available commands: codegen Generate code to interact with database records create-hive-table Import a table definition into Hive eval Evaluate a SQL statement and display the results export Export an HDFS directory to a database table ... See 'sqoop help COMMAND' for information on a specific command.
Display usage instructions for theimport
tool:
显示导入工具的使用说明:
$ bin/sqoop help import usage: sqoop import [GENERIC-ARGS] [TOOL-ARGS] Common arguments: --connect <jdbc-uri> Specify JDBC connect string --connection-manager <class-name> Specify connection manager class to use --driver <class-name> Manually specify JDBC driver class to use --hadoop-mapred-home <dir> Override $HADOOP_MAPRED_HOME --help Print usage instructions -P Read password from console --password <password> Set authentication password --username <username> Set authentication username --verbose Print more information while working --hadoop-home <dir> Deprecated. Override $HADOOP_HOME Import control arguments: --as-avrodatafile Imports data to Avro Data Files --as-sequencefile Imports data to SequenceFiles --as-textfile Imports data as plain text (default) ...
转载于:https://blog.51cto.com/chenchangqun/1229720