sqlserver，mysql到hive建表shell脚本
pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>untitled3</artifactId><version>1.0-SNAPSHOT</version><dependencies><dependency><groupId>org.apache.flume</groupId><artifactId>flume-ng-core</artifactId><version>1.9.0</version><scope>provided</scope></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.62</version></dependency></dependencies><build><plugins><plugin><artifactId>maven-compiler-plugin</artifactId><version>2.3.2</version><configuration><source>1.8</source><target>1.8</target></configuration></plugin><plugin><artifactId>maven-assembly-plugin</artifactId><configuration><descriptorRefs><descriptorRef>jar-with-dependencies</descriptorRef></descriptorRefs></configuration><executions><execution><id>make-assembly</id><phase>package</phase><goals><goal>single</goal></goals></execution></executions></plugin></plugins></build>
</project>
主程序
import java.util.ArrayList;/*** @Description 因sqlserver与mysql表结构不一样，故下面列出2者的获取方式demo，参考者注意复制正确对应的表结构**/
public class Utils {/**** 去除String数组中的空值*/private static String[] deleteArrayNull(String string[]) {String strArr[] = string;// step1: 定义一个list列表，并循环赋值ArrayList<String> strList = new ArrayList<String>();for (int i = 0; i < strArr.length; i++) {strList.add(strArr[i]);}// step2: 删除list列表中所有的空值while (strList.remove(null));while (strList.remove(""));// step3: 把list列表转换给一个新定义的中间数组，并赋值给它String strArrLast[] = strList.toArray(new String[strList.size()]);return strArrLast;}/**** 替换mysql或sqlserver字段类型所对应的hive的字段类型（可能会有遗漏，默认为string，检查时可自行新增缺失的类型）*/public static String findColumnType(String str) {str = str.toLowerCase();String type;if (str.startsWith("int")) {type = "int";} else if (str.startsWith("bigint")) {type = "bigint";} else if (str.startsWith("decimal(")) {type = "double";} else if (str.startsWith("bit")) {type = "boolean";} else if (str.startsWith("datetime")) {type = "string";} else if (str.startsWith("tinyint")){type = "int";}else {type = "string";}return type;}public static void main(String[] args) {// 复制sqlserver原表的建表语句（WITH关键词上面部分即可）
// String str9 = "create table [dbo].[StockReturnItemOperation] (\n" +
// "[ID] uniqueidentifier not null constraint PK_StockReturnItemOperation primary key,\n" +
// "[StockAuthorityId] uniqueidentifier not null,\n" +
// "[StockAuthorityName] nvarchar(50),\n" +
// "[Plant] nvarchar(50),\n" +
// "[StockDate] date not null,\n" +
// "[MaterialNumber] nvarchar(50),\n" +
// "[StockTempItemId] uniqueidentifier,\n" +
// "[Quantity] decimal(9,3),\n" +
// "[Comment] nvarchar(250),\n" +
// "[[From] int not null,\n" +
// "[Action] int not null,\n" +
// "[Operator] nvarchar(50),\n" +
// "[CreatedBy] uniqueidentifier not null,\n" +
// "[CreatedTime] datetime not null,\n" +
// "[SjfoodStockId] uniqueidentifier\n" +
// ")";// 复制mysql原表的建表语句String str9 = "CREATE TABLE `cn_staff` (\n" +" `id` varchar(128) NOT NULL,\n" +" `username` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\n" +" `user_id` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\n" +" `superior_id` varchar(128) DEFAULT NULL,\n" +" `finger_print_number` varchar(78) DEFAULT NULL,\n" +" `department_id` varchar(128) DEFAULT NULL,\n" +" `department_name` varchar(128) DEFAULT NULL,\n" +" `fertility_status` tinyint(4) DEFAULT NULL,\n" +" `position_id` varchar(128) DEFAULT NULL,\n" +" `position_name` varchar(128) DEFAULT NULL,\n" +" `group_id` varchar(128) DEFAULT NULL,\n" +" `group_name` varchar(128) DEFAULT NULL,\n" +" `name_cn_long` varchar(128) DEFAULT NULL,\n" +" `name_en_long` varchar(128) DEFAULT NULL,\n" +" `gender` tinyint(4) DEFAULT NULL,\n" +" `marital_status` tinyint(4) DEFAULT NULL,\n" +" `dob` date DEFAULT NULL,\n" +" `nation` varchar(128) DEFAULT NULL,\n" +" `education` varchar(250) DEFAULT NULL,\n" +" `graduation_school` varchar(250) DEFAULT NULL,\n" +" `major` varchar(250) DEFAULT NULL,\n" +" `identity_card_number` varchar(128) DEFAULT NULL,\n" +" `phone_number` varchar(128) DEFAULT NULL,\n" +" `email` varchar(128) DEFAULT NULL,\n" +" `internship_start_date` datetime DEFAULT NULL,\n" +" `hired_date` datetime DEFAULT NULL,\n" +" `political_status` tinyint(4) DEFAULT NULL,\n" +" `household_category` varchar(128) DEFAULT NULL,\n" +" `household_location` varchar(128) DEFAULT NULL,\n" +" `bank_name` varchar(250) DEFAULT NULL,\n" +" `bank_number` varchar(120) DEFAULT NULL,\n" +" `contract_status` tinyint(4) DEFAULT NULL,\n" +" `work_place` varchar(250) DEFAULT NULL,\n" +" `emergency_contact` varchar(128) DEFAULT NULL,\n" +" `emergency_contact_phone_number` varchar(128) DEFAULT NULL,\n" +" `serial_number` varchar(250) DEFAULT NULL,\n" +" `address1` varchar(250) DEFAULT NULL,\n" +" `address2` varchar(250) DEFAULT NULL,\n" +" `position_status` tinyint(4) DEFAULT NULL,\n" +" `park_type` tinyint(4) DEFAULT NULL,\n" +" `created_date` datetime DEFAULT NULL,\n" +" `created_by` varchar(128) DEFAULT NULL,\n" +" `last_modified_date` datetime DEFAULT NULL,\n" +" `last_modified_by` varchar(128) DEFAULT NULL,\n" +" `terminated_date` datetime DEFAULT NULL COMMENT '离职日期',\n" +" `location_description` int(11) DEFAULT NULL,\n" +" `country_code` tinyint(4) DEFAULT NULL,\n" +" `driver_code` varchar(20) DEFAULT NULL,\n" +" `company_id` char(128) DEFAULT NULL,\n" +" `company_name` varchar(255) DEFAULT NULL,\n" +" `internship_end_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,\n" +" `entrance_guard_number` char(255) DEFAULT NULL,\n" +" `seat_number` char(20) DEFAULT NULL,\n" +" `current_address` varchar(250) DEFAULT NULL,\n" +" `job_type` varchar(255) DEFAULT NULL,\n" +" `belong_company` varchar(255) DEFAULT NULL,\n" +" `cooperation_state` tinyint(4) DEFAULT NULL,\n" +" `latest_terminated_date` date DEFAULT NULL,\n" +" `department_type` varchar(128) DEFAULT NULL,\n" +" `salary_type` tinyint(4) DEFAULT NULL,\n" +" `birthplace` varchar(255) DEFAULT NULL,\n" +" PRIMARY KEY (`id`) USING BTREE,\n" +" KEY `username` (`username`) USING BTREE,\n" +" KEY `user_id` (`user_id`) USING BTREE\n" +") ENGINE=InnoDB DEFAULT CHARSET=utf8";//mysqlString tableName = str9.split("` \\(\n")[0].split("`")[1].toLowerCase();String[] columnLine = str9.split("` \\(\n")[1].split("PRIMARY KEY \\(")[0].split(",\n");//sqlServer
// String tableName = str9.split("] \\(\n")[0].split("].\\[")[1];String tableName = str9.split("] \\(\n")[0].split("].\\[")[1].toLowerCase();
// String[] columnLine = str9.split("] \\(\n")[1].split("CONSTRAINT")[0].split(",\n");// System.out.println(tableName);
// System.out.println(columnLine.length);StringBuilder hiveSqlStr = new StringBuilder();StringBuilder dataxColumnStr = new StringBuilder();StringBuilder dataxSqlStr = new StringBuilder();int columnNum = 0;hiveSqlStr.append("drop table if exists hr_cn.ods_").append(tableName).append("_full;\n").append(("create external table IF NOT EXISTS hr_cn.ods_").concat(tableName).concat("_full (")).append("\n");for (String line: columnLine) {String[] column = deleteArrayNull(line.replace("\n", "").split(" "));if (column.length >= 2) {String columnName = column[0].replace("[","").replace("]", "").replace("`", "");String typeName = findColumnType(column[1]);dataxColumnStr.append(columnName).append(",").append(" ");dataxSqlStr.append("{\"name\": \"").append(columnName).append("\",\"type\": \"").append(typeName).append("\"},").append("\n");
// hiveSqlStr.append(" " + columnName.toLowerCase() + " " + typeName + ",").append("\n");hiveSqlStr.append(" " + columnName + " " + typeName + " comment '' ,").append("\n");columnNum ++;}}dataxColumnStr.delete(dataxColumnStr.length() - 2,dataxColumnStr.length());dataxSqlStr.delete(dataxSqlStr.length() - 2,dataxSqlStr.length());hiveSqlStr.delete(hiveSqlStr.length() - 2,hiveSqlStr.length());hiveSqlStr.append("\n) comment '设置的打卡时间'\n").append("partitioned by (dt string)\n").append("row format delimited fields terminated by '\\001'").append("\nNULL DEFINED AS ''").append("\n LOCATION '/warehouse/hr_cn/ods/ods_"+tableName+"_full"+"';");System.out.println(hiveSqlStr);//判断表结构列数与输出列数是否一致
// if (columnLine.length == columnNum) {
// System.out.println(dataxColumnStr.toString() + "\n");
// System.out.println(dataxSqlStr.toString() + "\n");
// System.out.println(hiveSqlStr.toString() + "\n");
// } else {
// System.out.println((columnLine.length - 1 == columnNum) + " columnLine length : " + (columnLine.length - 1) + " ==== " + columnNum);
// }
// System.out.println(tableName + " 表结构列数为 ： " + (columnLine.length - 1));}
}
