将sql service数据库中的数据迁移到clickhourse中_sqlserver2008 迁移clickhouse-优快云博客

文章介绍了如何在ClickHouse中创建与SQLServer对应结构的表，然后利用DataX工具进行数据迁移，具体包括配置JSON文件以定义读取SQLServer中的数据并写入到ClickHouse的过程。

首先我们要根据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的压缩文件的，我放在我的资源这里了