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>
mysql
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class mysql {
private static String[] deleteArrayNull(String string[]) {
ArrayList<String> strList = new ArrayList<>();
// Loop through the input array and add non-null elements to the ArrayList
for (String str : string) {
if (str != null && !str.isEmpty()) {
strList.add(str);
}
}
// Convert the ArrayList back to an array of strings
return strList.toArray(new String[0]);
}
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 = "decimal"; // Assuming Hive's decimal type matches SQL's decimal type
} else if (str.startsWith("bit")) {
type = "int"; // Assuming mapping to INT in Hive
} else if (str.startsWith("datetime") || str.startsWith("date") || str.startsWith("time")) {
type = "string"; // These date-time related types mapped to STRING in Hive
} else if (str.startsWith("float")) {
type = "float";
} else if (str.startsWith("double")) {
type = "double";
} else if (str.startsWith("boolean")) {
type = "boolean";
} else {
type = "string"; // Defaulting to STRING for unmatched types
}
return type;
}
public static void main(String[] args) {
//mysql
String str9 = "CREATE TABLE `sys_user` (\n" +
" `id` varchar(32) NOT NULL COMMENT '',\n" +
" `username` varchar(100) DEFAULT NULL COMMENT '登录账号',\n" +
" `realname` varchar(100) DEFAULT NULL COMMENT '真实姓名',\n" +
" `password` varchar(255) DEFAULT NULL COMMENT '密码',\n" +
" `salt` varchar(45) DEFAULT NULL COMMENT 'md5密码盐',\n" +
" `avatar` varchar(255) DEFAULT NULL COMMENT '头像',\n" +
" `birthday` datetime DEFAULT NULL COMMENT '生日',\n" +
" `sex` tinyint(1) DEFAULT NULL COMMENT '性别(0-默认未知,1-男,2-女)',\n" +
" `email` varchar(45) DEFAULT NULL COMMENT '电子邮件',\n" +
" `phone` varchar(45) DEFAULT NULL COMMENT '电话',\n" +
" `org_code` varchar(64) DEFAULT NULL COMMENT '登录会话的机构编码',\n" +
" `status` tinyint(1) DEFAULT NULL COMMENT '性别(1-正常,2-冻结)',\n" +
" `del_flag` tinyint(1) DEFAULT NULL COMMENT '删除状态(0-正常,1-已删除)',\n" +
" `third_id` varchar(100) DEFAULT NULL COMMENT '第三方登录的唯一标识',\n" +
" `third_type` varchar(100) DEFAULT NULL COMMENT '第三方类型',\n" +
" `activiti_sync` tinyint(1) DEFAULT NULL COMMENT '同步工作流引擎(1-同步,0-不同步)',\n" +
" `work_no` varchar(100) DEFAULT NULL COMMENT '工号，唯一键',\n" +
" `post` varchar(100) DEFAULT NULL COMMENT '职务，关联职务表',\n" +
" `telephone` varchar(45) DEFAULT NULL COMMENT '座机号',\n" +
" `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',\n" +
" `create_time` datetime DEFAULT NULL COMMENT '创建时间',\n" +
" `update_by` varchar(32) DEFAULT NULL COMMENT '更新人',\n" +
" `update_time` datetime DEFAULT NULL COMMENT '更新时间',\n" +
" `user_identity` tinyint(1) DEFAULT NULL COMMENT '身份（1普通成员 2上级）',\n" +
" `depart_ids` longtext COMMENT '负责部门',\n" +
" `rel_tenant_ids` varchar(100) DEFAULT NULL COMMENT '多租户标识',\n" +
" `client_id` varchar(64) DEFAULT NULL COMMENT '设备ID',\n" +
" PRIMARY KEY (`id`) USING BTREE,\n" +
" UNIQUE KEY `uniq_sys_user_work_no` (`work_no`) USING BTREE,\n" +
" UNIQUE KEY `uniq_sys_user_username` (`username`) USING BTREE,\n" +
" UNIQUE KEY `uniq_sys_user_phone` (`phone`) USING BTREE,\n" +
" UNIQUE KEY `uniq_sys_user_email` (`email`) USING BTREE,\n" +
" KEY `idx_su_username` (`username`) USING BTREE,\n" +
" KEY `idx_su_status` (`status`) USING BTREE,\n" +
" KEY `idx_su_del_flag` (`del_flag`) USING BTREE\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表'";
//mysql
String tableName = str9.split("` \\(\n")[0].split("`")[1].toLowerCase();
String[] columnLines = str9.split("` \\(\n")[1].split("PRIMARY KEY \\(")[0].split(",\n");
StringBuilder hiveSqlStr = new StringBuilder();
// Begin building the Hive SQL script
hiveSqlStr.append("drop table if exists hr_cn.ods_").append(tableName).append("_full;\n")
.append("create external table if not exists hr_cn.ods_").append(tableName).append("_full (").append("\n");
String tableComment = "";
Pattern tableCommentPattern = Pattern.compile("COMMENT='([^']*)'");
Matcher tableCommentMatcher = tableCommentPattern.matcher(str9);
if (tableCommentMatcher.find()) {
tableComment = tableCommentMatcher.group(1);
}
// Regular expression to match comments in SQL
Pattern commentPattern = Pattern.compile("COMMENT '([^']*)'");
for (String line : columnLines) {
String[] column = deleteArrayNull(line.replace("\n", "").split(" "));
if (column.length >= 2) {
String columnName = column[0].replace("[", "").replace("]", "").replace("`","").toLowerCase();
Matcher matcher = commentPattern.matcher(line);
String comment = "";
if (columnName.equals("id")) {
comment = "id"; // Set comment as "id" if column name is "ID"
}
if (matcher.find()) {
comment = matcher.group(1);
}
if (columnName.equals("id") && comment.isEmpty()) {
comment = "id"; // Set default comment as "id" if the column name is "id" and the comment is empty
}
String typeName = findColumnType(column[1]);
hiveSqlStr.append(" ").append(columnName).append(" ").append(typeName).append(" comment '").append(comment).append("',\n");
}
}
hiveSqlStr.delete(hiveSqlStr.length() - 2, hiveSqlStr.length());
hiveSqlStr.append("\n) comment '").append(tableComment).append("'\n")
.append("partitioned by (dt string)\n")
.append("ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\001'\nNULL DEFINED AS ''\nLOCATION '/warehouse/hr_cn/ods/ods_")
.append(tableName).append("_full';");
System.out.println(hiveSqlStr);
}
}
sqlserver
**
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class sqlserver {
private static String[] deleteArrayNull(String string[]) {
ArrayList<String> strList = new ArrayList<>();
// Loop through the input array and add non-null elements to the ArrayList
for (String str : string) {
if (str != null && !str.isEmpty()) {
strList.add(str);
}
}
// Convert the ArrayList back to an array of strings
return strList.toArray(new String[0]);
}
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 = "decimal"; // Assuming Hive's decimal type matches SQL's decimal type
} else if (str.startsWith("bit")) {
type = "int"; // Assuming mapping to INT in Hive
} else if (str.startsWith("datetime") || str.startsWith("date") || str.startsWith("time")) {
type = "string"; // These date-time related types mapped to STRING in Hive
} else if (str.startsWith("float")) {
type = "float";
} else if (str.startsWith("double")) {
type = "double";
} else if (str.startsWith("boolean")) {
type = "boolean";
} else {
type = "string"; // Defaulting to STRING for unmatched types
}
return type;
}
public static void main(String[] args) {
//sql server
String str9 = "create table [dbo].[evaluate] (\n" +
"[id] uniqueidentifier not null ,\n" +
"[createddate] datetime not null comment '创建日期',\n" +
"[createdby] uniqueidentifier not null comment '创建日期',\n" +
"[lastmodifieddate] datetime not null comment '创建日期',\n" +
"[lastmodified]by] uniqueidentifier not null comment '创建日期',\n" +
"[staffid] uniqueidentifier not null comment '创建日期',\n" +
"[score] int not null,\n" +
"[comment] nvarchar(150)\n" +
")";
//sqlserver
String tableName = str9.split("] \\(\n")[0].split("].\\[")[1].toLowerCase();
String[] columnLines = str9.split("] \\(\n")[1].split("CONSTRAINT")[0].split(",\n");
StringBuilder hiveSqlStr = new StringBuilder();
int columnNum = 0;
// Begin building the Hive SQL script
hiveSqlStr.append("drop table if exists hr_cn.ods_").append(tableName).append("_full;\n")
.append("create external table if not exists hr_cn.ods_").append(tableName).append("_full (").append("\n");
// Regular expression to match comments in SQL
Pattern commentPattern = Pattern.compile(".*?\\[([^\\]]+)\\].*?comment\\s+'([^']*)'");
for (String line : columnLines) {
String[] column = deleteArrayNull(line.replace("\n", "").split(" "));
if (column.length >= 2) {
String columnName = column[0].replace("[", "").replace("]", "").replace("`","").toLowerCase();
Matcher matcher = commentPattern.matcher(line);
String comment = "";
if (columnName.equals("id")) {
comment = "id"; // Set comment as "id" if column name is "ID"
}
if (matcher.find()) {
comment = matcher.group(2);
}
if (columnName.equals("id") && comment.isEmpty()) {
comment = "id"; // Set default comment as "id" if the column name is "id" and the comment is empty
}
String typeName = findColumnType(column[1]);
hiveSqlStr.append(" ").append(columnName).append(" ").append(typeName).append(" comment '").append(comment).append("',\n");
columnNum++;
}
}
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'\nNULL DEFINED AS ''\nLOCATION '/warehouse/hr_cn/ods/ods_")
.append(tableName).append("_full';");
System.out.println(hiveSqlStr);
}
}
453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
