Как проанализировать столбец фрейма данных с динамическими записями и записать его в разные столбцы, поддерживая связь с таблицей
cur = snow_connection.cursor()
data = snow_connection.cursor().execute("SELECT x,y,z FROM abc.testing_view2").fetchall()
df = pd.DataFrame(data)
df.columns = ['x','y','z']
print(df)
temp_col=df.iloc[:,2]
print(temp_col)
for i in range (len(df.iloc[:,0])):
for j in range(len(df.iloc[:,1])):
for k in range(len(temp_col)):
json_dict = str(json.loads(str(k)))
print(json_dict)
# accessing values in the dictionary
portfolio_name = str(json_dict[0])
portfolio_id = str(json_dict[1])
for l in range(len(portfolio_name)):
for m in range(len(portfolio_id)):
# created a new dictionary with all three columns
new_dict = {'name': portfolio_name, 'portfolio_id': portfolio_id,'x': ['X'], 'y': ['Y']}
print(new_dict)
new_data = []
new_data.append(new_dict)
new_df = pd.DataFrame(new_data)
Проблема в том, что столбец z (который имеет запись json с именем и идентификатором) имеет динамические записи, т.е. может содержать только одну запись с именем и идентификатором, а также более 1. но он будет отображаться только с одним x и y. Поэтому я должен записать эти данные в несколько строк, т.е. если столбец z содержит одну запись, он должен записать данные в 4 столбца x, y, имя и идентификатор, а если столбец z содержит 2 записи, то он должен записать данные и создать 2 строки .
Я перебираю этот столбец и записываю его в словарь, чтобы поддерживать отношение, однако он не читает словарь json, который я создал для имени и идентификатора.
Образец данных:
{
"document_portfolio": "[{\"name\": \"MWP FT Income ICP\", \"portfolio_id\": \"31a01afd-1f69-e617-ade3-d7c1895c4461\"}, {\"name\": \"MWP Tactical ETP IMG\", \"portfolio_id\": \"13281ca6-a9a7-d361-1cf2-07e6fa3e283a\"}]",
"document_uuid": "28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3",
"user_id": "00u9vj92B0ZPUMU9b5d5"
}
{
"document_portfolio": "[{\"name\": \"tesying\", \"portfolio_id\": \"59d26651-3484-e7ef-9ece-f7194d7639e0\"}]","document_uuid": "1cf1ca8e-f0e9-844b-11d6-0d05302fb777",
"user_id": "00u5flkeths3G668k5d7"
}
Ожидаемый результат:
portfolio_id name portfolio_uuid user_id
31a01afd-1f69-e617-ade3-d7c1895c4461 MWP FT Income ICP 28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3 00u9vj92B0ZPUMU9b5d5
13281ca6-a9a7-d361-1cf2-07e6fa3e283a MWP Tactical ETP IMG 28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3 00u9vj92B0ZPUMU9b5d5
Пример логики снежинки:
CREATE OR REPLACE VIEW port_test_vw AS
SELECT
LATERAL FLATTEN(PARSE_JSON(data:document_portfolio):portfolio) flattened,
flattened:name::string AS portfolio_name,
flattened:portfolio_id::string AS portfolio_id,
FROM XYZ;
Используйте pd.read_json вместо json.loads
@MikeWalton .... Я добавил образцы данных .... Я искал документацию по снежинкам. Пробовал использовать метод Flatten, но снова не сработало.
Спасибо @jqurious ... поэтому я немного изменил логику, и я могу анализировать записи в столбце динамических данных ... однако он читает только первую запись, но не может выполнять итерацию, если имеется более одной записи. Может ли кто-нибудь указать на ошибку здесь?
Это можно сделать в одном запросе Snowflake, используя LATERAL FLATTEN, а не извлекать данные в python и перебирать набор записей.
@MikeWalton .... Я уже пробовал это решение, но получаю ошибку с синтаксисом. Пробовал и метод UNNEST.
Ваш синтаксис для бокового сплющивания неверен. Я поставил ответ ниже для вас, если интересно.
Спасибо @MikeWalton.... Я могу создать представление, используя этот синтаксис. однако данные не загружаются. Я добавляю логику здесь
Ответы 3
Я исправил вышеуказанную проблему. Надеюсь, что если у кого-то тоже есть, вы можете повторно использовать эту логику:
df = pd.DataFrame(data)
df.columns = ['DOCUMENT_ID', 'DOCUMENT_UUID', 'USER_ID', 'DOCUMENT_PORTFOLIO']
for row in data:
document_id = row[0]
document_uuid = row[1]
user_id = row[2]
json_dict = row[3]
portfolio_data = json.loads(json_dict)
for item in portfolio_data:
portfolio_name = item['name']
portfolio_id = item['portfolio_id']
new_row = {'DOCUMENT_ID': document_id, 'DOCUMENT_UUID': document_uuid, 'USER_ID': user_id, 'PORTFOLIO_NAME': portfolio_name, 'PORTFOLIO_ID': portfolio_id}
df = df.append(new_row, ignore_index=True)
#print(df)
table_name = 'XYZ'
with snow_connection.cursor() as cursor:
cursor.execute(f"CREATE OR REPLACE TABLE {table_name}(DOCUMENT_ID INT,DOCUMENT_UUID TEXT,USER_ID TEXT,PORTFOLIO_NAME TEXT,PORTFOLIO_ID TEXT)")
df.to_sql(table_name, snow_connection, index=False, if_exists='append')
Вот вам синтаксис Snowflake SQL:
with x as (
select parse_json('{
"document_portfolio": [{"name": "MWP FT Income ICP", "portfolio_id": "31a01afd-1f69-e617-ade3-d7c1895c4461"}, {"name": "MWP Tactical ETP IMG", "portfolio_id": "13281ca6-a9a7-d361-1cf2-07e6fa3e283a"}],
"document_uuid": "28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3",
"user_id": "00u9vj92B0ZPUMU9b5d5"
}')::variant as var
)
SELECT y.value:name::string AS portfolio_name,
y.value:portfolio_id::string AS portfolio_id
FROM x,
lateral flatten(input=>var:document_portfolio) y;
CREATE OR REPLACE VIEW document_testing_view222
AS SELECT
data:document_portfolio::STRING AS document_portfolio,
doc_port.value:name::string AS portfolio_name,
doc_port.value:portfolio_id::string AS portfolio_id
FROM pcs.document,
lateral flatten(input=>PARSE_JSON(data:document_portfolio):document_portfolio) doc_port;
Было бы полезно, если бы вы предоставили некоторые примеры данных и ожидаемый результат. Это кажется чем-то, что может быть проще решить с помощью прямого SQL Snowflake, но трудно сказать, исходя из вашего вопроса.