Read the table structures of all tables in hive, and create tables and indexes in the new hive database.

Read the table structure in hive. This article contains the table class, the field class is used to encapsulate the table structure, and it will be OK after a rough look.

(Change the code format)

1. Table class

Public class table {

Private string tablename;

Private list <field> field;

Public table (){

}

Public table (string tablename, list <field> Field ){

This. tablename = tablename;

This. Field = field;

}

Public String gettablename (){

Return tablename;

}

Public void settablename (string tablename ){

This. tablename = tablename;

}

Public list <field> getfield (){

Return field;

}

Public void setfield (list <field> Field ){

This. Field = field;

}

@ Override

Public String tostring (){

Return "table {" + "tablename =" + tablename + ", field =" + field + '}';

}

}

2. Field Class

Public class field {

Private string columnname;

Private string typename;

Private int columnsize;

Private int decimal_digits;

Private int nullable;

Public field (){

}

Public field (string columnname, string typename, int columnsize, int decimal_digits, int nullable ){

This. columnname = columnname;

This. typename = typename;

This. columnsize = columnsize;

This. decimal_digits = decimal_digits;

This. nullable = nullable;

}

Public String getcolumnname (){

Return columnname;

}

Public void setcolumnname (string columnname ){

This. columnname = columnname;

}

Public String gettypename (){

Return typename;

}

Public void settypename (string typename ){

This. typename = typename;

}

Public int getcolumnsize (){

Return columnsize;

}

Public void setcolumnsize (INT columnsize ){

This. columnsize = columnsize;

}

Public int getdecimal_digits (){

Return decimal_digits;

}

Public void setdecimal_digits (INT decimal_digits ){

This. decimal_digits = decimal_digits;

}

Public int getnullable (){

Return nullable;

}

Public void setnullable (INT nullable ){

This. nullable = nullable;

}

@ Override

Public String tostring (){

Return "field {" + "columnname =" + columnname + ", typename =" + typename + ", columnsize =" + columnsize + ", decimal_digits =" + decimal_digits + ", nullable = "+ nullable + '}';

}

}

3. Main Methods

Public list <Table> Export (){

String showtablessql = "show tables ";

List <Table> tablelist = new arraylist <Table> ();

List <string> tablenamelist = new arraylist <string> ();

Connection conn = hiveutil. getconnection ();

Statement stmt = NULL;

Resultset tablers = NULL; // the metadata of the database.

Resultset colrs = NULL; // store table metadata

Try {

Stmt = conn. createstatement ();

// Obtain the table name

Tablers = stmt.exe cutequery (showtablessql );

While (tablers. Next ()){

String table = tablers. getstring (1 );

Tablenamelist. Add (table );

}

// Obtain the table structure

Field field = NULL;

Table = NULL;

For (INT I = 0; I <tablenamelist. Size (); I ++ ){

String desctablesql = "describe ";

List <field> fieldlist = new arraylist <field> ();

Desctablesql = desctablesql + tablenamelist. Get (I). Trim (); // concatenate SQL

Colrs = stmt.exe cutequery (desctablesql );

While (colrs. Next ()){

Field = new field ();

Field. setcolumnname (colrs. getstring (1 ));

Field. settypename (colrs. getstring (2); // test the size.

Fieldlist. Add (field );

}

Table = new table ();

Table. settablename (tablenamelist. Get (I). Trim ());

Table. setfield (fieldlist );

Tablelist. Add (table );

}

} Catch (sqlexception ex ){

Logger. getlogger (exportoracletable. Class. getname (). Log (level. Severe, null, ex );

} Finally {

If (colrs! = NULL ){

Try {

Colrs. Close ();

} Catch (sqlexception ex ){

Logger. getlogger (exportoracletable. Class. getname (). Log (level. Severe, null, ex );

}

}

If (tablers! = NULL ){

Try {

Tablers. Close ();

} Catch (sqlexception ex ){

Logger. getlogger (exportoracletable. Class. getname (). Log (level. Severe, null, ex );

}

}

If (Conn! = NULL ){

Try {

Conn. Close ();

} Catch (sqlexception ex ){

Logger. getlogger (exportoracletable. Class. getname (). Log (level. Severe, null, ex );

}

}

}

Return tablelist;

}

4. Create a table in the new database

Public void createtabletohive (list <Table> List ){

Connection conn = hiveutil. gettargetconnection ();

Statement stmt = NULL;

Try {

For (Table: List ){

Stringbuffer SQL = new stringbuffer ();

Stringbuffer schemasql = new stringbuffer ();

Stringbuffer indexsql = new stringbuffer ();

SQL. append ("CREATE TABLE if not exists ");

SQL. append (table. gettablename (); // Create Table Test

For (INT I = 0; I <Table. getfield (). Size (); I ++ ){

SQL. append ("(" + table. getfield (). get (I ). getcolumnname () + "" + table. getfield (). get (I ). gettypename (); // create table test (name varchar (2), age INT)

// If (table. getfield (). Get (I). getcolumnsize () = 0) {// The condition needs to be tested.

// SQL. append ("(" + Table. getfield (). Get (I). getcolumnsize () + ")");

//}

If (I = table. getfield (). Size ()-1 ){

Continue;

}

SQL. append (",");

}

// Execute

Stmt = conn. createstatement ();

Stmt.exe cutequery (SQL. tostring (). Trim ());

// Stmt.exe cutequery (schemasql );

}

Conn. Commit ();

} Catch (sqlexception ex ){

Logger. getlogger (createhivetable. Class. getname (). Log (level. Severe, null, ex );

} Finally {

If (stmt! = NULL ){

Try {

Stmt. Close ();

} Catch (sqlexception ex ){

Logger. getlogger (createhivetable. Class. getname (). Log (level. Severe, null, ex );

}

}

If (Conn! = NULL ){

Try {

Conn. Close ();

} Catch (sqlexception ex ){

Logger. getlogger (createhivetable. Class. getname (). Log (level. Severe, null, ex );

}

}

}

}

This article is from "I will work" blog, please be sure to keep this source http://meijia.blog.51cto.com/8684191/1563874

Read the table structures of all tables in hive, and create tables and indexes in the new hive database.