Я пытаюсь прочитать базу данных Oracle
и записать в таблицу Redshift
, используя PySpark
.
# Reading data from Oracle
oracle_df = spark.read \
.format("jdbc") \
.option("url", oracle_url) \
.option("dbtable", oracle_table) \
.option("user", oracle_user) \
.option("password", oracle_password) \
.option("driver", oracle_driver_name) \
.load()
Выбор всего нескольких столбцов:
selected_df = oracle_df.select("Col1", "Col2")
Схема печати:
selected_df.printSchema()
|-- Col1: decimal(38,10) (nullable = true)
|-- Col2: decimal(38,10) (nullable = true)
Я преобразовал все столбцы в строку:
for col_name in selected_df.schema.names:
selected_df = selected_df.withColumn(
col_name,
coalesce(selected_df[col_name].cast(StringType()), lit(""))
)
|-- Col1: string (nullable = false)
|-- Col2: string (nullable = false)
Когда я записываю Dataframe в Redshift, я получаю ошибку o323.save. ORA-01722: invalid number
.
# Redshift Connection Configuration
redshift_connection_name = 'redshift_connection'
redshift_connection_options = glueContext.extract_jdbc_conf(redshift_connection_name)
redshift_url = redshift_connection_options["fullUrl"]
redshift_user = redshift_connection_options["user"]
redshift_password = redshift_connection_options["password"]
redshift_driver_name = "com.amazon.redshift.jdbc42.Driver"
redshift_table = "test_schema.main_last"
# Writing data to Redshift
selected_df.write \
.format("jdbc") \
.option("driver", redshift_driver_name) \
.option("url", redshift_url) \
.option("dbtable", redshift_table) \
.option("user", redshift_user) \
.option("password", redshift_password) \
.option("tempdir", "s3://glue-temp/") \
.mode("overwrite") \
.save()
Я убедился, что Redshift DDL:
CREATE TABLE test_schema.main_last (
Col1 character varying(256) ENCODE lzo,
Col2 character varying(256) ENCODE lzo,
Я предполагаю, что возникла проблема с преобразованием в строку, попробуйте использовать что-то вроде этого:
from awsglue.dynamicframe import DynamicFrame
from awsglue.transforms import ApplyMapping
# Convert the DataFrame to a DynamicFrame
dynamic_frame = DynamicFrame.fromDF(selected_df, glueContext, "dynamic_frame")
# Apply the mapping
mapped_dynamic_frame = ApplyMapping.apply(
frame=dynamic_frame,
mappings=[
("Col1", "Col1", "string"),
("Col2", "Col2", "string")
],
transformation_ctx = "applymapping1"
)
# Convert the mapped DynamicFrame back to a DataFrame
selected_df = mapped_dynamic_frame.toDF()
Обновление1:
Пожалуйста, попробуйте выполнить преобразование следующим образом вместо того, чтобы делать это на стороне PySpark:
query = "(SELECT TO_CHAR(col1) AS col1, TO_CHAR(col2) AS col2 FROM oracle_table)"
oracle_df = spark.read \
.format("jdbc") \
.option("url", oracle_url) \
.option("query", query) \
.option("user", oracle_user) \
.option("password", oracle_password) \
.option("driver", oracle_driver_name) \
.load()
Для преобразования вы можете использовать CAST или TO_CHAR, как показано выше.
Кроме того, как я уже говорил ранее, вам необходимо убедиться, что «числовые строки» действительны, как указано в части «Действие» первоначального ответа ниже.
Первоначальный ответ:
Согласно документации Oracle вот причина и действия, которые необходимо предпринять для устранения ошибки ORA-01722:
Причина. Попытка преобразования символьной строки столбца или выражения в число не удалась, поскольку строка символов не является допустимым числовым литералом. В арифметических функциях или выражениях можно использовать только числовые поля или символьные поля, содержащие числовые данные. К датам можно добавлять или вычитать только числовые поля. Если в сообщении об ошибке появляется «UNISTR», это значение несовместимо с национальным набором символов и не может быть представлено напрямую.
Действие: Используйте выражение LIKE, чтобы определить проблемное значение. Убедитесь, что он содержит только цифры, знак, десятичный разделитель и символ «E» или «e», и повторите операцию.
Подводя итог в контексте вашего вопроса, возникает ошибка при преобразовании строки в число, когда вы пытаетесь записать selected_df
в Redshift.
Теперь, пожалуйста, поправьте меня, если я ошибаюсь, похоже, вы вообще не хотите, чтобы преобразование произошло. Вам нужны сами струны.
Итак, чтобы разобраться с этим, я продолжу обращаться к документации Amazon Redshift.
Возможное решение: вы можете установить тип столбца следующим образом, как показано в документации Amazon Redshift здесь в разделе Интеграция Amazon Redshift с Apache Spark:
Чтобы установить тип столбца, используйте поле
redshift_type
.
columnTypeMap.foreach { case (colName, colType) =>
val metadata = new MetadataBuilder().putString("redshift_type", colType).build()
df = df.withColumn(colName, df(colName).as(colName, metadata))
}
Приведенный выше фрагмент кода написан на Scala, поэтому я написал эквивалент PySpark:
for colName in df.columns:
metadata = {"redshift_type": "varchar"} # can be {"redshift_type": "char"} or {"redshift_type": "character"} or try different types that might work
df = df.withColumn(colName, df[colName].cast(StringType()).alias(colName, metadata=metadata))
Дополнительную информацию можно найти в документации Redshift по преобразованию типов данных. (Примечание. Эта документация относится к драйверу Amazon Redshift JDBC версии 2.1 — при необходимости проверьте документацию на наличие правильной версии в соответствии с вашей средой).
После этого вы можете попробовать снова записать фрейм данных.
Кроме того, просто для полноты решения — вы также можете проверить кодировки столбцов здесь, в разделе Кодировки сжатия в документации Redshift, где упоминается, что:
Столбцам, которые определены как типы данных
CHAR
илиVARCHAR
, назначается сжатиеLZO
.
Также я попробовал выполнить преобразование, чтобы все стало varchar и не было ошибок несоответствия типов.
когда я печатаю столбец фрейма данных, он говорит, что все является строкой print(spark_data_frame.dtypes)
. Если все является строкой, почему возникает ошибка недопустимого номера?
@Shabarinathk Возможно, драйвер пытается преобразовать «числовые строки» в числа. Чтобы найти точную причину, необходимо покопаться в документах драйверов. Итак, либо вам нужно убедиться, что «числовые строки» действительны, как указано в части ответа «Действие», либо вам нужно выполнить описанные выше шаги, чтобы сохранить их в виде varchar. Сначала проверьте, можете ли вы записать фрейм данных или нет после выполнения описанных выше шагов.
Я попытался запустить отправленный вами фрагмент кода Pyspark, но не смог записать фрейм данных.
Забудьте о написании фрейма данных, я получаю ту же ошибку при печати фрейма данных с помощью df.show(). An error occurred while calling o105.showString. ORA-01722: invalid number
@Shabarinathk Понятно. Похоже, проблема в том, что когда вы читаете данные из базы данных Oracle, драйвер делает что-то скрытое, вызывающее эту ошибку. Я предлагаю вам выполнить преобразование только на стороне Oracle - и просто прочитать уже преобразованные данные. Я обновлю свой ответ.
@Shabarinathk Есть какие-нибудь новости по этому поводу?
ORA-01722: недопустимый номер — это ошибка ORACLE, вызванная неудачной попыткой преобразования в число, явное или неявное. Что вы делаете после (вне Oracle) в данном случае не имеет значения. Команда Select вашего Oracle вернула эту ошибку.
Откуда возникает ошибка:
# Reading data from Oracle
oracle_df = spark.read \
.format("jdbc") \
.option("url", oracle_url) \
.option("dbtable", oracle_table) \
.option("user", oracle_user) \
.option("password", oracle_password) \
.option("driver", oracle_driver_name) \
.load()
Приведенный выше код запускает оператор «Выбрать [список столбцов] из oracle_table» в базе данных Oracle, и этот оператор выдает ошибку ORA-01722 Неверный номер.
Возможное решение:
Поскольку ошибка возникает при чтении данных из Oracle, убедитесь, что вы контролируете, что и как вы читаете. Возможно ли, что вы загружаете данные из представления Oracle, а не из таблицы? Это похоже на ошибку в определении представления и/или данных представления, из которых вы читаете. Если это так, то решить эту проблему будет сложнее, если у вас нет доступа к представлению.
Используйте опцию запроса вместо dbtable.
# Reading data from Oracle
# Use query option instead of dbtable
#
# query_0 test query to be sure that the logic works
query_0 = "select 'A' as COL1, 1 as COL2 from dual"
#
# query_1 reads original data
query_1 = "select COL1, COL2 from oracle_table"
#
# query_2 converts original data to char on Oracle db side
query_2 = "select To_Char(COL1) as COL1, To_Char(COL2) as COL2 from oracle_table"
oracle_df = spark.read \
.format("jdbc") \
.option("url", oracle_url) \
.option("query", query_2)\
.option("user", oracle_user) \
.option("password", oracle_password) \
.option("driver", oracle_driver_name) \
.load()
Цель состоит в том, чтобы либо изолировать «поврежденные» данные, либо создать обходной путь (с помощью функции Oracle TO_CHAR()) и загрузить ваши данные.
Начните с query_0, чтобы убедиться, что структура, драйвер, соединение и логика работают нормально.
Если вы хотите узнать, какой столбец (если это столбец) выдает ошибку, начните запрос только с одного столбца с исходными данными и добавляйте остальные (один за другим), пока не возникнет ошибка (query_1), что означает, что столбец, добавленный последним, поврежден. . Если все отдельные столбцы выдают одну и ту же ошибку, возможно, вы читаете ошибочное представление.
Если вы хотите загрузить данные столбцов в виде строк, объявите свой запрос, выполняющий преобразование на стороне базы данных Oracle (query_2). Преобразованные значения должны быть в порядке, просто позаботьтесь о столбцах, которые могут иметь тип данных DATE или TIMESTAMP в oracle. Если и это не помогло, вы, вероятно, читаете ошибочное представление.
Обработка полученных данных после их успешной загрузки.
Если вы по-прежнему получаете ту же ошибку, значит, проблема не в выбранных данных, а, скорее всего, вы читаете представление Oracle (почти уверен, что это представление) с ошибкой, которой нет в списке выбора (данных) представления – это находится в какой-то другой части оператора Select, например, Где или Иметь или где-то еще. Чтобы решить эту проблему, кто-то должен исправить определение представления на стороне Oracle, протестировать его и убедиться, что оно работает нормально.
Вот лишь несколько распространенных примеров команд Select, заканчивающихся ошибкой ORA-01722.
-- depending on your data - explicite conversion
Select To_Number('a23') "COL_NAME" From Dual;
-- ORA-01722: invalid number
Select Cast('a23' as Number) "COL_NAME" From Dual;
-- ORA-01722: invalid number
-- depending on your data and/or data manipulation - implicite conversion
Select 'a23' * 3 "COL_NAME" From Dual;
-- ORA-01722: invalid number
-- depending on your db settings of NLS parameters - if decimal point is comma, but dot is used when selecting the data
Select '2.3' * 3 "COL_NAME" From Dual;
-- ORA-01722: invalid number
Работа с типом данных DATE и манипуляциями с датами или математическими вычислениями дат также может вызвать эту ошибку.
НЕПРАВИЛЬНЫЙ ПРОСМОТР ОБРАЗЦА 1 (данные COL1):
Create Table TEST_TBL AS
( Select 'A' as A_LETTER, 1 as A_NUMBER, To_Date('01.01.2024', 'dd.mm.yyyy') as A_DATE From Dual Union All
Select 'B', 2, To_Date('02.01.2024', 'dd.mm.yyyy') From Dual Union All
Select 'C', 3, To_Date('03.01.2024', 'dd.mm.yyyy') From Dual
) ;
-- Table TEST_TBL created.
Create View TEST_VIEW AS
Select A_LETTER, A_NUMBER, A_DATE,
A_LETTER + A_NUMBER as COL1 -- expresion when run against the data will raise the error
From TEST_TBL;
-- View TEST_VIEW created.
При выборе любого столбца, кроме COL1, ошибки не возникнет.
Select A_LETTER From TEST_VIEW;
/*
A_LETTER
---------
A
B
C */
Select A_LETTER, A_NUMBER, A_DATE From TEST_VIEW;
/*
A_LETTER A_NUMBER A_DATE
-------- ---------- --------
A 1 01.01.24
B 2 02.01.24
C 3 03.01.24 */
... при выборе COL1 возникает ошибка...
Select A_LETTER, A_NUMBER, A_DATE, COL1 From TEST_VIEW;
-- ORA-01722: invalid number
ОБРАЗЕЦ НЕПРАВИЛЬНОГО ПРОСМОТРА 2 (пункт Где):
Create View TEST_VIEW AS
Select A_LETTER, A_NUMBER, A_DATE
From TEST_TBL
Where A_LETTER + A_NUMBER > 1;
-- View TEST_VIEW created.
Выбор любого столбца из такого представления приведет к ошибке.
Select A_LETTER From TEST_VIEW;
-- ORA-01722: invalid number
ОБРАЗЕЦ ОШИБОЧНОГО ПРОСМОТРА 3 – с проблемой данных (в столбце A_LETTER):
Если данные базовой таблицы такие, как показано ниже:
Select * From TEST_TBL;
/*
A_LETTER A_NUMBBER A_DATE
-------- --------- ----------
11 1 01.01.24
12 2 02.01.24
13C 3 03.01.24 */
... здесь столбец A_LETTER содержит значения, которые можно неявно преобразовать в число (кроме последней строки).
Если представление определено следующим образом:
Create View TEST_VIEW AS
Select A_LETTER, A_NUMBER, A_DATE, A_LETTER + A_NUMBER as COL1
From TEST_TBL
Where A_LETTER + A_NUMBER > 1;
-- View TEST_VIEW created.
... и если попытаться выбрать все данные - выдастся ошибка:
Select A_LETTER, A_NUMBER, A_DATE, COL1
From TEST_VIEW
-- ORA-01722: invalid number
... но если вы знаете/подозреваете, что столбец A_LETTER может содержать неправильные данные - вы можете исключить такие строки и получить все оставшиеся данные (используя... Значение по умолчанию при ошибке преобразования при попытке преобразовать символьное значение в число)
Select A_LETTER, A_NUMBER, A_DATE, COL1
From TEST_VIEW
Where To_Number(A_LETTER Default 0 ON Conversion Error) != 0
/*
A_LETTER A_NUMBER A_DATE COL1
-------- ---------- -------- ----------
11 1 01.01.24 12
12 2 02.01.24 14 */
Почему мне выдает ошибку ORA, когда я использую для записи драйвер красного смещения?