快速⽣成sparksql创建carbondata表结构（同步mysql或sqlserver。。。
前⾔
当实时同步mysql或sqlserver很多表数据到carbondata时，经常要⼿动调整脚本涉及到的每个表的字段、类型及对应建表语句，耗费⼤量的机械⽐对粘贴复制⼯作时间、精⼒，下⾯介绍的脚本能快速调整好改动点，只需⼿动复制改⼀下即可（快速、准确）
需同步的mysql或sqlserver原表结构
//sqlserver
CREATE TABLE [dbo].[TOTicket](
乔家的儿女官配[Id] char(16) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ParkId] int NOT NULL,
[Qty] int NOT NULL,
[AgencySaleTicketClassId] int NULL,
[ValidStartDate] datetime NOT NULL,
[ValidDays] int NOT NULL,
[Price] decimal(18,2) NOT NULL,
[SalePrice] decimal(18,2) NOT NULL,
[SettlementPrice] decimal(18,2) NULL,
[Amount] decimal(18,2) NOT NULL,
[InparkCounts] int NOT NULL,
[InvoiceId] bigint NULL,
[TerminalId] int NULL,
[TOVoucherId] char(18) COLLATE Chinese_PRC_CI_AS NULL,
[Remark] varchar(512) COLLATE Chinese_PRC_CI_AS NULL,
[LastModificationTime] datetime NULL,
[LastModifierUserId] bigint NULL,快递丢失赔偿
[CreationTime] datetime NOT NULL,
[CreatorUserId] bigint NULL,
[ParkSettlementPrice] decimal(18,2) NULL,
[TicketSaleStatus] int DEFAULT ((0)) NOT NULL,
[TicketFormEnum] int DEFAULT ((0)) NOT NULL,
[SyncTicketType] int NULL,
[FirstInparkTime] datetime NULL,
[PrintTicketTime] datetime NULL,
[TOBodyId] char(21) COLLATE Chinese_PRC_CI_AS NOT NULL,
[RV] timestamp NOT NULL,
[IsFreeze] bit DEFAULT ((0)) NOT NULL,
[TOHeaderId] char(18) COLLATE Chinese_PRC_CI_AS DEFAULT '' NOT NULL,
[PId] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[AgencyDeriveTicketClassId] int DEFAULT ((0)) NOT NULL,
CONSTRAINT [PK_dbo.TOTicket] PRIMARY KEY NONCLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = O N)
ON [PRIMARY]
)高速铁路动车乘务
//mysql
CREATE TABLE `parkderiveticketclassrelation`(
`Id` bigint(20) NOT NULL,
`CreationTime` datetime(6) NOT NULL,
`CreatorUserId` bigint(20) NOT NULL,
`LastModificationTime` datetime(6) DEFAULT NULL,
`LastModifierUserId` bigint(20) NOT NULL,
`IsDeleted` bit(1) NOT NULL,
`DeletionTime` datetime(6) DEFAULT NULL,
`DeleterUserId` bigint(20) NOT NULL,
`ParkDeriveTicketClassId` int(11) NOT NULL DEFAULT '0',
`ParkId` int(11) NOT NULL,
`InParkBeginTime` varchar(10) DEFAULT NULL,
`InParkEndTime` varchar(10) DEFAULT NULL,
PRIMARY KEY (`Id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
⽬标结构（脚本⾃动⽣成）
脚本代码
★★★此脚本为初版临时⽤，需进⼀步优化，仅供参考
import org.apachemons.lang3.StringUtils;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName CarbonSql
* @Description //TODO
* @Author
* @Date 2020/8/18 10:46
* @Version 1.0
**/
public class CarbonSql {
/
/获取sqlServer表字段、是否主键、类型、表字段描述
public static String sqlServerSql(String tableName){
String sql ="SELECT\n" +
" cast(a.name as varchar) as columnName,\n" +
" cast(case when exists(SELECT 1 FROM sysobjects where xtype = 'PK' and parent_obj = a.id and name in (\n" +
" SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid))) then 'PR I' else '' end as varchar) as priKey,\n" +
" cast(b.name as varchar) as columnType, \n" +
" cast(isnull(g.[value], '') as varchar) as columnComment \n" +
"FROM\n" +
" syscolumns a\n" +
"left join\n" +
" systypes b\n" +
"on\n" +
" a.xusertype = b.xusertype\n" +
"inner join\n" +
" sysobjects d\n" +
"on\n" +
" a.id = d.id pe = 'U' and d.name <> 'dtproperties'\n" +
"left join\n" +
" syscomments e\n" +
"on\n" +
" a.cdefault = e.id\n" +
"left join\n" +
"ded_properties g\n" +
"on\n" +
" a.id = G.major_id lid = g.minor_id\n" +
"left join\n" +
"ded_properties f\n" +
"on\n" +
" d.id = f.major_id and f.minor_id = 0\n" +
"where\n" +
" d.name = '" + tableName + "' \n" +
"order by\n" +
" a.lorder";
return sql;
}
//获取mysql表字段、是否主键、类型、表字段描述
public static String mysqlSql(String tableName, String dataBase){
String sql ="Select COLUMN_NAME as columnName, COLUMN_KEY as priKey, DATA_TYPE as columnType, COLUMN_COMMENT as columnCom ment \n" +
"from INFORMATION_SCHEMA.COLUMNS \n" +
"Where table_name = '" + tableName + "' \n" +
"AND table_schema = '" + dataBase + "'";
return sql;
return sql;
}
//将sqlserver或者mysql字段类型转成carbondata对应的类型（若不满⾜需要，可⾃⾏修改）
public static String findColumnType2(String str){
str = LowerCase();
String type;
if(str.startsWith("int")|| str.startsWith("smallint")){
type="INT";
}else if(str.startsWith("bigint")){
type="LONG";
}else if(str.startsWith("decimal(")){
type="DOUBLE";
}else if(str.startsWith("bit")){
type="BOOLEAN";
}else if(str.startsWith("datetime")){
type="TIMESTAMP";
}else{
type="STRING";
}
return type;
}
//将结果写⼊⽂本中保存
public static void write(String ss) throws IOException {
String path ="E:\\tmp\\";
//将写⼊转化为流的形式
BufferedWriter bw = new BufferedWriter(new FileWriter(path, true));//true，则追加写⼊text⽂本
//⼀次写⼀⾏
// String ss ="测试数据";
bw.write(ss);
bw.flush();
//关闭流
bw.close();
System.out.println("写⼊成功");
}
//获取database信息
public static void findDataBase(String tableName){
//维度表与增量表划分
String incre_table =",toticket,taickettable3,parkderiveticketclassrelation,mysqltable2,table1,usertable2,tradetable1,"; String demi_table =",taickettable1,mysqltabl1,parkderiveticketclass,usertable1,tradetable2,taickettable2,table2,";
//表真实来源
String ticket_sqlserver =",taickettable1,taickettable2,toticket,taickettable3,";
String ticket_mysql =",mysqltabl1,parkderiveticketclass,parkderiveticketclassrelation,mysqltable2,";
String shop =",table1,table2,";
String user =",usertable1,usertable2,";
String trade =",tradetable1,tradetable2,";
谌的拼音String sourceType ="";
String source="";
String className ="";
String url ="";
String userName ="";
String password ="";
String dataBase ="";
String str ="," + tableName + ",";
if(ains(str)){
sourceType ="增量表";
}else{
sourceType ="维度表";
sourceType ="维度表";
}
System.out.println(sourceType);
//获取数据库连接
if(ains(str)){
source="ticket sqlserver";
className ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
url ="jdbc:sqlserver://:9527;DatabaseName=data2";
userName ="**";
password ="*************";
dataBase ="";
}else if(ains(str)){
source="ticket mysql";
className ="sql.cj.jdbc.Driver";
ains("parkderiveticketclass")|| ains("parkderiveticketclassrelation")
|| ains("mysqltable5")|| ains("mysqltable9")
|| ains("mysqltable222")){
url ="jdbc:mysql://:3306/data_one";
dataBase ="data_one";
}else{
url ="jdbc:mysql://:3306/data_two";
dataBase ="data_two";
}
userName ="***";
password ="*****************";
外星人玩转地球}else ains(str)){
source="shop";
className ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
url ="jdbc:sqlserver://:666666;DatabaseName=data999";
userName ="***";
password ="*******************";
dataBase ="";
}else ains(str)){
source="user数据";
className ="sql.cj.jdbc.Driver";
url ="jdbc:mysql://xxxxxxxxx:9527/user";
userName ="****";
password ="*******";
dataBase ="user";
}else ains(str)){
source="xxx数据";
className ="sql.cj.jdbc.Driver";
url ="jdbc:mysql://db:35845/case2222";
userName ="****";
password ="*******";
dataBase ="case2222";
}
南宁旅游景点大全createSql(className, url, userName, password, dataBase, tableName, sourceType, source);
}
public static void createSql(String className, String url, String userName, String password, String dataBase, String tableName, String sourceType, Strin g source){
//拼接carbon建表语句
StringBuffer stringBuffer = new StringBuffer();
ResultSet re = null;
Statement statement = null;
Connection con = null;
try {
Class.forName(className);
con = Connection(url, userName, password);
statement = ateStatement();
String sql ="";
if(dataBase.isEmpty()){
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论