将sql service数据库中的数据迁移到clickhourse中

首先我们要根据sql server数据库的表在clickhourse中对应的建立表：

CREATE TABLE IF NOT EXISTS default.PosSalSheet(
                 SheetID String,
                 PosType Int8,
                 Status Int8,
                 IsRefund Int8,
RefundStatus Int8,
SheetDate DateTime64,
AppName String,
ShopID String,
ShopName String,
DtbCYTypeID Int8,
BaseID String,
BaseName String,
SalesAmo Decimal128(4),
Amo Decimal128(4),
Discount Decimal32(4),
DisAmo Decimal128(4),
CouponAmo Decimal128(4),
ExchangeAmo Decimal128(4),
AdjAmo Decimal128(4),
FeeAmo Decimal128(4),
FreightAmo Decimal128(4),
PackagingAmo Decimal128(4),
PolybagAmo Decimal128(4),
TotalAmo Decimal128(4),
PayAmo  Decimal128(4),
ChangeAmo Decimal128(4),
CostAmo Decimal128(4),
CreatedDate DateTime64
             )
             ENGINE = MergeTree
             ORDER BY (SheetID )
             SETTINGS index_granularity = 8192;

然后这里我才用的是datax进行的数据迁移：

在服务器上打开解压好的datax目录，将写好的json语句放在job文件夹下，输入语句

python bin/datax.py  ./job/possalsheet.json

执行任务

possalsheet.json的内容如下：

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "sqlserverreader",
                    "parameter": {
                        "connection": [
                            {
                                "jdbcUrl": [
                                    "jdbc:sqlserver://47.106.64.222:1433;DatabaseName=HBBPosData"
                                ],
                                "querySql": [
                                    " select SheetID,PosType,Status,IsRefund,RefundStatus,SheetDate,AppName,ShopID,ShopName,DtbCYTypeID,BaseID,BaseName,SalesAmo,Amo,Discount,DisAmo,CouponAmo,ExchangeAmo,AdjAmo,FeeAmo,FreightAmo,PackagingAmo,PolybagAmo,TotalAmo,PayAmo,ChangeAmo,CostAmo,CreatedDate from PosSalSheet"
                                ]
                            }
                        ],
                        "username": "Hnust",
                        "password": "Hnust@202302061746YXJ.."
                    }
                },
                "writer": {
                    "name": "clickhousewriter",
                    "parameter": {
                        "username": "default",
                        "password": "123456",
                        "column": [ "*" ],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:clickhouse://192.168.186.154:8123/default",
                                "table": [
                                    "PosSalSheet"
                                ]
                            }
                        ],
                        "preSql": [],
                        "postSql": [],
                        "batchSize": 65536,
                        "batchByteSize": 134217728,
                        "dryRun": false,
                        "writeMode": "insert"
                    }
                }
              
            }
        ],
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}

最终完成装换，要datax的压缩文件的，我放在我的资源这里了