Outline error dealing with for Amazon Redshift Spectrum knowledge

Amazon Redshift is a completely managed, petabyte-scale knowledge warehouse service within the cloud. Amazon Redshift Spectrum means that you can question open format knowledge instantly from the Amazon Easy Storage Service (Amazon S3) knowledge lake with out having to load the info into Amazon Redshift tables. With Redshift Spectrum, you possibly can question open file codecs corresponding to Apache Parquet, ORC, JSON, Avro, and CSV. This function of Amazon Redshift permits a contemporary knowledge structure that means that you can question all of your knowledge to acquire extra full insights.

Amazon Redshift has a normal method of dealing with knowledge errors in Redshift Spectrum. Knowledge file fields containing any particular character are set to null. Character fields longer than the outlined desk column size get truncated by Redshift Spectrum, whereas numeric fields show the utmost quantity that may match within the column. With this newly added user-defined knowledge error dealing with function in Amazon Redshift Spectrum, now you can customise knowledge validation and error dealing with.

This function supplies you with particular strategies for dealing with every of the situations for invalid characters, surplus characters, and numeric overflow whereas processing knowledge utilizing Redshift Spectrum. Additionally, the errors are captured and visual within the newly created dictionary view SVL_SPECTRUM_SCAN_ERROR. You’ll be able to even cancel the question when an outlined threshold of errors has been reached.

Stipulations

To display Redshift Spectrum user-defined knowledge dealing with, we construct an exterior desk over a knowledge file with soccer league info and use that to indicate completely different knowledge errors and the way the brand new function presents completely different choices in coping with these knowledge errors. We’d like the next stipulations:

Answer overview

We use the info file for soccer leagues to outline an exterior desk to display completely different knowledge errors and the completely different dealing with strategies supplied by the brand new function to take care of these errors. The next screenshot exhibits an instance of the info file.

Notice the next within the instance:

• The membership identify can usually be longer than 15 characters
• The league identify might be usually be longer than 20 characters
• The membership identify Barcelôna contains an invalid character
• The column nspi contains values which can be greater than SMALLINT vary

Then we create an exterior desk (see the next code) to display the brand new user-defined dealing with:

• We outline the club_name and league_name shorter than they need to to display dealing with of surplus characters
• We outline the column league_nspi as SMALLINT to display dealing with of numeric overflow
• We use the brand new desk property data_cleansing_enabled to allow customized knowledge dealing with

CREATE EXTERNAL TABLE schema_spectrum_uddh.soccer_league
(
  league_rank smallint,
  prev_rank   smallint,
  club_name   varchar(15),
  league_name varchar(20),
  league_off  decimal(6,2),
  league_def  decimal(6,2),
  league_spi  decimal(6,2),
  league_nspi smallint
)
ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY 'nl'
saved as textfile
LOCATION 's3://uddh-soccer/league/'
desk properties ('skip.header.line.rely'='1','data_cleansing_enabled'='true');

Invalid character knowledge dealing with

With the introduction of the brand new desk and column property invalid_char_handling, now you can select the way you take care of invalid characters in your knowledge. The supported values are as follows:

• DISABLED – Characteristic is disabled (no dealing with).
• SET_TO_NULL – Replaces the worth with null.
• DROP_ROW – Drops the entire row.
• FAIL – Fails the question when an invalid UTF-8 worth is detected.
• REPLACE – Replaces the invalid character with a alternative. With this feature, you need to use the newly launched desk property replacement_char.

The desk property can work over the entire desk or only a column degree. Moreover, you possibly can outline the desk property throughout create time or later by altering the desk.

Once you disable user-defined dealing with, Redshift Spectrum by default units the worth to null (much like SET_TO_NULL):

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('invalid_char_handling'='DISABLED');

Once you change the setting of the dealing with to DROP_ROW, Redshift Spectrum merely drops the row that has an invalid character:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('invalid_char_handling'='DROP_ROW');

Once you change the setting of the dealing with to FAIL, Redshift Spectrum fails and returns an error:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('invalid_char_handling'='FAIL');

Once you change the setting of the dealing with to REPLACE and select a alternative character, Redshift Spectrum replaces the invalid character with the chosen alternative character:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('invalid_char_handling'='REPLACE','replacement_char'='?');

Surplus character knowledge dealing with

As talked about earlier, we outlined the columns club_name and league_name shorter than the precise contents of the corresponding fields within the knowledge file.

With the introduction of the brand new desk property surplus_char_handling, you possibly can select from a number of choices:

• DISABLED – Characteristic is disabled (no dealing with)
• TRUNCATE – Truncates the worth to the column dimension
• SET_TO_NULL – Replaces the worth with null
• DROP_ROW – Drops the entire row
• FAIL – Fails the question when a price is simply too giant for the column

Once you disable the user-defined dealing with, Redshift Spectrum defaults to truncating the excess characters (much like TRUNCATE):

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('surplus_char_handling' = 'DISABLED');

Once you change the setting of the dealing with to SET_TO_NULL, Redshift Spectrum merely units to NULL the column worth of any area that’s longer than the outlined size:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('surplus_char_handling' = 'SET_TO_NULL');

Once you change the setting of the dealing with to DROP_ROW, Redshift Spectrum drops the row of any area that’s longer than the outlined size:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('surplus_char_handling' = 'DROP_ROW');

Once you change the setting of the dealing with to FAIL, Redshift Spectrum fails and returns an error:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('surplus_char_handling' = 'FAIL');

We have to disable the user-defined knowledge dealing with for this knowledge error earlier than demonstrating the subsequent kind of error:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('surplus_char_handling' = 'DISABLED');

Numeric overflow knowledge dealing with

For this demonstration, we outlined league_nspi deliberately SMALLINT (with a spread to carry from -32,768 to +32,767) to indicate the out there choices for knowledge dealing with.

With the introduction of the brand new desk property numeric_overflow_handling, you possibly can select from a number of choices:

• DISABLED – Characteristic is disabled (no dealing with)
• SET_TO_NULL – Replaces the worth with null
• DROP_ROW – Replaces every worth within the row with NULL
• FAIL – Fails the question when a price is simply too giant for the column

After we take a look at the supply knowledge, we will observe that the highest 5 international locations have extra factors than the SMALLINT area can deal with.

Once you disable the user-defined dealing with, Redshift Spectrum defaults to the utmost quantity the numeric knowledge kind can deal with, for our case SMALLINT can deal with as much as 32767:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('numeric_overflow_handling' = 'DISABLED');

Once you select SET_TO_NULL, Redshift Spectrum units to null the column with numeric overflow:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('numeric_overflow_handling' = 'SET_TO_NULL');

Once you select DROP_ROW, Redshift Spectrum drops the row containing the column with numeric overflow:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('numeric_overflow_handling' = 'DROP_ROW');

Once you select FAIL, Redshift Spectrum fails and returns an error:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('numeric_overflow_handling' = 'FAIL');

We have to disable the user-defined knowledge dealing with for this knowledge error earlier than demonstrating the subsequent kind of error:

alter desk schema_spectrum_uddh.soccer_league
set desk properties ('numeric_overflow_handling' = 'DISABLED');

Cease queries at MAXERROR threshold

It’s also possible to select to cease the question if it reaches a sure threshold in errors through the use of the newly launched parameter spectrum_query_maxerror:

Set spectrum_query_maxerror to 7;

The next screenshot exhibits that the question ran efficiently.

Nevertheless, should you lower this threshold to a decrease quantity, the question fails as a result of it reached the preset threshold:

Set spectrum_query_maxerror to six;

Error logging

With the introduction of the brand new user-defined knowledge dealing with function, we additionally launched the brand new view svl_spectrum_scan_error, which lets you view a helpful pattern set of the logs of errors. The desk incorporates the question, file, row, column, error code, dealing with motion that was utilized, in addition to the unique worth and the modified (ensuing) worth. See the next code:

SELECT *
FROM svl_spectrum_scan_error
the place location = 's3://uddh-soccer/league/spi_global_rankings.csv'

Clear up

To keep away from incurring future expenses, full the next steps:

1. Delete the Amazon Redshift cluster created for this demonstration. For those who had been utilizing an current cluster, drop the created exterior desk and exterior schema.
2. Delete the S3 bucket.
3. Delete the AWS Glue Knowledge Catalog database.

Conclusion

On this publish, we demonstrated Redshift Spectrum’s newly added function of user-defined knowledge error dealing with and confirmed how this function supplies the pliability to take a user-defined strategy to take care of knowledge exceptions in processing exterior information. We additionally demonstrated how the logging enhancements present transparency on the errors encountered in exterior knowledge processing while not having to write down extra customized code.

We stay up for listening to from you about your expertise. When you’ve got questions or solutions, please depart a remark.

In regards to the Authors

Ahmed Shehata is a Knowledge Warehouse Specialist Options Architect with Amazon Internet Providers, primarily based out of Toronto.

Milind Oke is a Knowledge Warehouse Specialist Options Architect primarily based out of New York. He has been constructing knowledge warehouse options for over 15 years and makes a speciality of Amazon Redshift. He’s targeted on serving to prospects design and construct enterprise-scale well-architected analytics and resolution help platforms.