How Do I Fix Incorrect Data in an OBS Foreign Table Caused by Newline Characters in OBS File Fields?

Symptom

CREATE TABLE test06 (name string, id int, no string) USING csv OPTIONS (path "obs://dli-test-001/test.csv");

The file contains the following information (example):

Jordon,88,"aa
bb"

A carriage return exists between aa and bb in the last field. As a result, he data in the test06 table is displayed as follows:

name	id	classno
Jordon	88	aa
bb"	null	null

Solution

When creating an OBS foreign table, set multiLine to true to specify that the column data contains CRLF characters. The following is an example to solve the problem:

CREATE TABLE test06 (name string, id int, no string) USING csv OPTIONS (path "obs://dli-test-001/test.csv",multiLine=true);