Как устранить следующую ошибку AWS Glue при записи в Redshift с помощью Spark: «ORA-01722: неверный номер»?

Я пытаюсь прочитать базу данных 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,
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
2
0
214
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Я предполагаю, что возникла проблема с преобразованием в строку, попробуйте использовать что-то вроде этого:

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.

Почему мне выдает ошибку ORA, когда я использую для записи драйвер красного смещения?

Shabari nath k 24.06.2024 15:08

Также я попробовал выполнить преобразование, чтобы все стало varchar и не было ошибок несоответствия типов.

Shabari nath k 24.06.2024 15:25

когда я печатаю столбец фрейма данных, он говорит, что все является строкой print(spark_data_frame.dtypes). Если все является строкой, почему возникает ошибка недопустимого номера?

Shabari nath k 24.06.2024 15:27

@Shabarinathk Возможно, драйвер пытается преобразовать «числовые строки» в числа. Чтобы найти точную причину, необходимо покопаться в документах драйверов. Итак, либо вам нужно убедиться, что «числовые строки» действительны, как указано в части ответа «Действие», либо вам нужно выполнить описанные выше шаги, чтобы сохранить их в виде varchar. Сначала проверьте, можете ли вы записать фрейм данных или нет после выполнения описанных выше шагов.

Vikas Sharma 24.06.2024 19:27

Я попытался запустить отправленный вами фрагмент кода Pyspark, но не смог записать фрейм данных.

Shabari nath k 25.06.2024 06:59

Забудьте о написании фрейма данных, я получаю ту же ошибку при печати фрейма данных с помощью df.show(). An error occurred while calling o105.showString. ORA-01722: invalid number

Shabari nath k 25.06.2024 07:43

@Shabarinathk Понятно. Похоже, проблема в том, что когда вы читаете данные из базы данных Oracle, драйвер делает что-то скрытое, вызывающее эту ошибку. Я предлагаю вам выполнить преобразование только на стороне Oracle - и просто прочитать уже преобразованные данные. Я обновлю свой ответ.

Vikas Sharma 26.06.2024 23:48

@Shabarinathk Есть какие-нибудь новости по этому поводу?

Vikas Sharma 29.06.2024 16:38
Ответ принят как подходящий

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):

  • query_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 (пункт Где):

  • query_1 и query_2 вызывают ошибку для каждого выбранного столбца
  • неисправности нет в списке выбора данных (она находится в пункте Where)
  • вы не можете ничего получить без вмешательства в код представления или, возможно, в базовые данные таблицы (таблиц), если данные являются проблемой
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    */

Другие вопросы по теме