使用 Python 简化数据集成(ELT 方法)

 

目录

先决条件

连接到 Salesforce 并提取数据

将数据加载到 Redshift 暂存表中

执行 ELT 的存储过程

结论

---

将来自 Salesforce 和 Oracle 等多个来源的数据集成到 Amazon Redshift 中对于希望集中分析的组织至关重要。本文演示了如何连接到 Salesforce 和 Oracle、使用 SOQL 和 SQL 查询提取数据、将其加载到 Redshift 暂存表中，以及使用 Redshift 存储过程执行转换，所有这些都是通过 Python 脚本编排的。

先决条件

• Salesforce：使用必要的 API 权限访问 Salesforce。
• Oracle：使用必要的查询权限访问 Oracle 数据库。
• Amazon Redshift：现有的 Redshift 集群。
• Python：安装了必要的库（simple_salesforce、cx_Oracle、boto3、psycopg2）。

连接到 Salesforce 并提取数据

首先，让我们连接到 Salesforce 并使用 SOQL 提取数据。

 

from simple_salesforce import Salesforce
import pandas as pd

# Salesforce credentials
sf = Salesforce(username='your_username', 
                password='your_password', 
                security_token='your_security_token')

# SOQL query to fetch data from Salesforce
query = "SELECT Id, Name, AccountNumber FROM Account"
response = sf.query_all(query)

# Convert response to a DataFrame
data_sf = pd.DataFrame(response['records']).drop(columns='attributes')
print(data_sf.head())

• 库导入：我们从中导入，以便于Salesforce API交互和数据操作。Salesforcesimple_salesforcepandas
• Salesforce 连接：我们使用提供的凭据建立与 Salesforce 的连接。最好的实现方法是从配置文件或使用环境变量将用户名和密码作为参数传递，不要对密码进行硬编码。
• SOQL 查询：我们执行 SOQL（Salesforce 对象查询语言）查询以从对象中检索特定字段 （， ， ）。Id Name AccountNumber Account
• 数据转换：将响应转换为 pandas DataFrame，以便于操作和分析。

 连接到 Oracle 并提取数据

接下来，让我们连接到 Oracle 并使用 SQL 提取数据。 

 

import cx_Oracle

# Oracle credentials and connection details
oracle_dsn = cx_Oracle.makedsn("your_oracle_host", "your_oracle_port", service_name="your_service_name")
conn_oracle = cx_Oracle.connect(user="your_username", password="your_password", dsn=oracle_dsn)

# SQL query to fetch data from Oracle
sql_query = "SELECT ID, NAME, ACCOUNT_NUMBER FROM ACCOUNTS"
data_oracle = pd.read_sql(sql_query, con=conn_oracle)
print(data_oracle.head())

# Close Oracle connection
conn_oracle.close()

• 库导入：我们导入 Oracle 数据库连接。cx_Oracle
• Oracle 连接：我们使用提供的凭据建立与 Oracle 的连接。
• SQL 查询：我们执行 SQL 查询以从表中检索特定字段 （， ， ）。ID NAME ACCOUNT_NUMBER ACCOUNTS
• 数据转换：将结果转换为 pandas DataFrame，以便于操作和分析。

将数据加载到 Redshift 暂存表中

现在，我们将从 Salesforce 和 Oracle 中提取的数据加载到 Redshift 暂存表中。

import boto3
import psycopg2
from io import StringIO

# Redshift credentials and connection details
redshift_host = 'your_redshift_host'
redshift_db = 'your_database'
redshift_user = 'your_user'
redshift_password = 'your_password'
redshift_port = 5439

# Connect to Redshift
conn_redshift = psycopg2.connect(
    host=redshift_host,
    dbname=redshift_db,
    user=redshift_user,
    password=redshift_password,
    port=redshift_port
)
cur_redshift = conn_redshift.cursor()

# Create staging tables (if they don't exist)
create_sf_table_query = """
CREATE TABLE IF NOT EXISTS staging_account_sf (
    Id VARCHAR(18),
    Name VARCHAR(255),
    AccountNumber VARCHAR(40)
);
"""
create_oracle_table_query = """
CREATE TABLE IF NOT EXISTS staging_account_oracle (
    ID VARCHAR(18),
    NAME VARCHAR(255),
    ACCOUNT_NUMBER VARCHAR(40)
);
"""
cur_redshift.execute(create_sf_table_query)
cur_redshift.execute(create_oracle_table_query)
conn_redshift.commit()

# Load Salesforce data into staging table
csv_buffer_sf = StringIO()
data_sf.to_csv(csv_buffer_sf, index=False, header=False)
csv_buffer_sf.seek(0)
cur_redshift.copy_from(csv_buffer_sf, 'staging_account_sf', sep=',')
conn_redshift.commit()

# Load Oracle data into staging table
csv_buffer_oracle = StringIO()
data_oracle.to_csv(csv_buffer_oracle, index=False, header=False)
csv_buffer_oracle.seek(0)
cur_redshift.copy_from(csv_buffer_oracle, 'staging_account_oracle', sep=',')
conn_redshift.commit()

• 库导入：我们为 AWS 交互、PostgreSQL/Redshift 连接和内存中文件操作导入。boto3psycopg2StringIO
• Redshift 连接：我们使用提供的凭据建立与 Redshift 的连接。
• 创建临时表：如果暂存表尚不存在，我们会创建临时表（用于 Salesforce 数据和 Oracle 数据）。staging_account_sfstaging_account_oracle
• 数据加载：将数据帧转换为 CSV 格式，并使用 加载到相应的临时表中，从而有效地将数据加载到 Redshift 中。copy_from

 

执行 ELT 的存储过程

一旦数据进入暂存表，我们就可以在 Redshift 中调用存储过程来转换数据并将其加载到最终表中。

# Call stored procedure for transformation
stored_procedure_query = "CALL transform_data_procedure();"
cur_redshift.execute(stored_procedure_query)
conn_redshift.commit()

# Verify data in the final table
verify_query = "SELECT * FROM final_account_table LIMIT 10;"
cur_redshift.execute(verify_query)
for row in cur_redshift.fetchall():
    print(row)

# Close the connection
cur_redshift.close()
conn_redshift.close()

 

• 存储过程调用：我们在 Redshift 中调用一个存储过程 （），它执行必要的转换并将数据加载到最终表中。这将 ELT（提取、加载、转换）逻辑封装在数据库中，利用 Redshift 的处理能力。transform_data_procedure
• 数据验证：我们运行一个查询来验证数据是否已正确转换并加载到最终表 （） 中。final_account_table
• 关闭连接：最后，我们关闭光标和数据库连接以清理资源。

 

结论

此脚本演示了从 Salesforce 和 Oracle 提取数据、将其加载到 Amazon Redshift 以及使用存储过程执行 ELT 操作的完整工作流程。这种方法利用了每个组件的优势：用于 CRM 和关系数据的 Salesforce 和 Oracle，用于编排的 Python，以及用于可扩展数据转换的 Redshift。

通过在 Redshift 中集中数据，组织可以执行更全面的分析，并从其 Salesforce 和 Oracle 数据中获得有价值的见解，从而实现更好的决策和运营效率。