快速⽣成sparksql创建carbondata表结构（同步mysql或sqlrver。。。
前⾔
当实时同步mysql或sqlrver很多表数据到carbondata时，经常要⼿动调整脚本涉及到的每个表的字段、类型及对应建表语句，耗费⼤量的机械⽐对粘贴复制⼯作时间、精⼒，下⾯介绍的脚本能快速调整好改动点，只需⼿动复制改⼀下即可（快速、准确）
婴儿多大可以吃盐和油需同步的mysql或sqlrver原表结构
//sqlrver
CREATE TABLE [dbo].[TOTicket](
[Id] char(16) COLLATE Chine_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 Chine_PRC_CI_AS NULL,
[Remark] varchar(512) COLLATE Chine_PRC_CI_AS NULL,
[LastModificationTime] datetime NULL,
[LastModifierUrId] bigint NULL,
[CreationTime] datetime NOT NULL,
[CreatorUrId] 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 Chine_PRC_CI_AS NOT NULL,
[RV] timestamp NOT NULL,
[IsFreeze] bit DEFAULT ((0)) NOT NULL,
[TOHeaderId] char(18) COLLATE Chine_PRC_CI_AS DEFAULT '' NOT NULL,
[PId] nvarchar(30) COLLATE Chine_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,
`CreatorUrId` bigint(20) NOT NULL,
`LastModificationTime` datetime(6) DEFAULT NULL,
`LastModifierUrId` bigint(20) NOT NULL,
`IsDeleted` bit(1) NOT NULL,
`DeletionTime` datetime(6) DEFAULT NULL,
`DeleterUrId` 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 s.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(ca 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' el '' 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.xurtype = b.xurtype\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 dataBa){
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 = '" + dataBa + "'";
return sql;
return sql;
}
//将sqlrver或者mysql字段类型转成carbondata对应的类型（若不满⾜需要，可⾃⾏修改）
public static String findColumnType2(String str){
str = LowerCa();
String type;
if(str.startsWith("int")|| str.startsWith("smallint")){
type="INT";
}el if(str.startsWith("bigint")){
汽车报警灯光标志图解
type="LONG";
}el if(str.startsWith("decimal(")){
type="DOUBLE";
}el if(str.startsWith("bit")){
type="BOOLEAN";
}el if(str.startsWith("datetime")){
type="TIMESTAMP";
}el{
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.clo();
System.out.println("写⼊成功");
}
//获取databa信息
public static void findDataBa(String tableName){
//维度表与增量表划分
String incre_table =",toticket,taickettable3,parkderiveticketclassrelation,mysqltable2,table1,urtable2,tradetable1,"; String demi_table =",taickettable1,mysqltabl1,parkderiveticketclass,urtable1,tradetable2,taickettable2,table2,";
//表真实来源
String ticket_sqlrver =",taickettable1,taickettable2,toticket,taickettable3,";
String ticket_mysql =",mysqltabl1,parkderiveticketclass,parkderiveticketclassrelation,mysqltable2,";
String shop =",table1,table2,";
String ur =",urtable1,urtable2,";
String trade =",tradetable1,tradetable2,";
String sourceType ="";
String source="";
String className ="";
String url ="";
String urName ="";
String password ="";
String dataBa ="";
String str ="," + tableName + ",";
if(ains(str)){
sourceType ="增量表";
}el{
sourceType ="维度表";
sourceType ="维度表";
}
System.out.println(sourceType);
//获取数据库连接
if(ains(str)){
source="ticket sqlrver";
className ="com.microsoft.sqlrver.jdbc.SQLServerDriver";
url ="jdbc:sqlrver://:9527;DatabaName=data2";
urName ="**";
password ="*************";
dataBa ="";
}el 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";
dataBa ="data_one";
}el{
url ="jdbc:mysql://:3306/data_two";
dataBa ="data_two";
}
urName ="***";
password ="*****************";
}el ains(str)){
source="shop";
className ="com.microsoft.sqlrver.jdbc.SQLServerDriver";
url ="jdbc:sqlrver://:666666;DatabaName=data999";
urName ="***";
password ="*******************";
观沧海图片
dataBa ="";
}el ains(str)){
source="ur数据";
className ="sql.cj.jdbc.Driver";
url ="jdbc:mysql://xxxxxxxxx:9527/ur";
urName ="****";
password ="*******";
dataBa ="ur";
}el ains(str)){
source="xxx数据";
className ="sql.cj.jdbc.Driver";
url ="jdbc:mysql://db.com:35845/ca2222";
urName ="****";
password ="*******";
dataBa ="ca2222";
}
createSql(className, url, urName, password, dataBa, tableName, sourceType, source);
除与除以的区别}
public static void createSql(String className, String url, String urName, String password, String dataBa, 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, urName, password);
statement = ateStatement();
String sql ="";
if(dataBa.isEmpty()){
