Python实现流式同步：ADB PostgreSQL到clickhouse

背景：

我们的公司数据仓库当前使用的是PostgreSQL数据库。虽然PostgreSQL是一个功能强大的关系型数据库管理系统（RDBMS），但它并不是专门为OLAP（联机分析处理）设计的，因此往往无法完全满足数据分析师的需求，这也使得它经常遭到数据分析师的吐槽。

为了提升数据分析的效率并提供更好的分析体验，我们决定接入ClickHouse数据库。ClickHouse是一种高性能的列式数据库，特别适合处理OLAP的场景，能够大大优化复杂查询和数据分析的速度与性能。

因此，我们需要将PostgreSQL中存储的一些业务数据同步到ClickHouse中。这不仅能使数据分析师更方便地使用数据，还能充分利用ClickHouse的强大能力，为我们的数据分析需求提供有力的支持。

前期准备：

• python环境
• Clickhouse数据库环境
• Postgres数据库环境
• ck环境需要提前建好目标表

import psycopg2
from clickhouse_driver import Client
import threading
import queue
import time

# PostgreSQL连接信息
pg_conn_info = {
    'dbname': '数据库名称',
    'user': '用户名',
    'password': '密码',
    'host': '数据库地址',
    'port': '端口号'
}

# ClickHouse连接信息
ck_conn_info = {
    'host': '数据库地址',
    'port': '端口号',
    'user': '用户名',
    'password': '密码',
    'database': '数据库名称'
}

读取Postgres数据库中的业务数据

def pg_reader():
    try:
        conn = psycopg2.connect(**pg_conn_info)
        cursor = conn.cursor()
        ##字段不能有null值，可以变成空字符
        query = "sql语句"
        cursor.execute(query)
        
        while True:
            rows = cursor.fetchmany(1000)
            if not rows:
                break
            for row in rows:
                ##往队列中插入数据
                data_queue.put(row)
        cursor.close()
        conn.close()
    except Exception as e:
        print(f"PostgreSQL Error: {e}")

向Clickhouse数据库中写入数据

def ck_writer():
    try:
        client = Client(**ck_conn_info)
        rows=[]
        sn=0
        while True:
            sn=sn+1
            ##从队列中获取数据
            row = data_queue.get()
            if row is None:  ##判断是否结束
                break
            rows.append(row)
            if sn%3000==0:
                client.execute('INSERT INTO ck表名 VALUES', rows)
                sn=0
                rows.clear()
        client.execute('INSERT INTO ck表名 VALUES', rows)
        client.disconnect()
        data_queue.task_done()
    except Exception as e:
        print(f"ClickHouse Error: {e}")
        # 重试逻辑
        time.sleep(5)
        ck_writer()

使用多线程启动脚本

# 启动PostgreSQL读取线程
pg_reader_thread = threading.Thread(target=pg_reader)
pg_reader_thread.start()

# 启动ClickHouse写入线程
num_threads = 2  ##线程个数，2个
threads = []
for i in range(num_threads):
    thread = threading.Thread(target=ck_writer)
    thread.start()
    threads.append(thread)

# 阻塞队列直到所有任务完成
data_queue.join()

# 等待线程结束
pg_reader_thread.join()
for i in range(num_threads):
    data_queue.put(None) ##插入结束标记
for thread in threads:
    thread.join()

总结：

• 使用了队列（quene）的先进先出原理
• 实操中发现，队列的读取速度比写入速度慢，因此使用了多线程的读取

附录代码

import psycopg2
from clickhouse_driver import Client
import threading
import queue
import time

# PostgreSQL连接信息
pg_conn_info = {
    'dbname': '数据库名称',
    'user': '用户名',
    'password': '密码',
    'host': '数据库地址',
    'port': '端口号'
}

# ClickHouse连接信息
ck_conn_info = {
    'host': '数据库地址',
    'port': '端口号',
    'user': '用户名',
    'password': '密码',
    'database': '数据库名称'
}

# 数据队列
data_queue = queue.Queue(maxsize=100000)
# 从PostgreSQL读取数据的线程
def pg_reader():
    try:
        conn = psycopg2.connect(**pg_conn_info)
        cursor = conn.cursor()
        ##字段不能有null值，可以变成空字符
        query = "sql语句"
        cursor.execute(query)
        
        while True:
            rows = cursor.fetchmany(1000)
            if not rows:
                break
            for row in rows:
                ##往队列中插入数据
                data_queue.put(row)
        cursor.close()
        conn.close()
    except Exception as e:
        print(f"PostgreSQL Error: {e}")

# 向ClickHouse写入数据的线程
def ck_writer():
    try:
        client = Client(**ck_conn_info)
        rows=[]
        sn=0
        while True:
            sn=sn+1
            ##从队列中获取数据
            row = data_queue.get()
            if row is None:  ##判断是否结束
                break
            rows.append(row)
            if sn%3000==0:
                client.execute('INSERT INTO ck表名 VALUES', rows)
                sn=0
                rows.clear()
        client.execute('INSERT INTO ck表名 VALUES', rows)
        client.disconnect()
        data_queue.task_done()
    except Exception as e:
        print(f"ClickHouse Error: {e}")
        # 重试逻辑
        time.sleep(5)
        ck_writer()

# 启动PostgreSQL读取线程
pg_reader_thread = threading.Thread(target=pg_reader)
pg_reader_thread.start()

# 启动ClickHouse写入线程
num_threads = 2  ##线程个数，2个
threads = []
for i in range(num_threads):
    thread = threading.Thread(target=ck_writer)
    thread.start()
    threads.append(thread)

# 阻塞队列直到所有任务完成
data_queue.join()

# 等待线程结束
pg_reader_thread.join()
for i in range(num_threads):
    data_queue.put(None) ##插入结束标记
for thread in threads:
    thread.join()