Got it

Replace null with \N in the data exported by spark-sql using insert overwrite

Latest reply: Nov 21, 2021 15:40:10 361 2 3 0 0

Hello, everyone!

At a site, null is replaced with \N in the data exported by Spark-SQL using insert overwrite. This is a normal function of Hive. This problem occurs when spark-sql, spark-beeline, and spark-shell are used to operate the parquet and carbondata tables.

This problem can be solved by improving the SQL statement.

Symptom:

Table creation statement:

CREATE TABLE carbon_test1(imei string, deviceInformationId int) STORED BY'org.apache.carbondata.format';

Load data:

LOAD DATA inpath 'hdfs://hacluster/tmp/carbon_test1.csv' into table carbon_test1 options ('DELIMITER'=',', 'FILEHEADER'='imei, deviceInformationId');

The content of the carbon_test1.csv file is as follows:

111,
222,

Run the select statement to view the following information:

1

Export statement:

spark.sql  ("insert overwrite local directory '/tmp/carbon' row format limited fields terminated by '\t' select * from carbon_test1")

After the export, it is found that the null value is replaced with \N:

111 \N
222 \N

Solution:

Change the insert overwrite statement to

insert overwrite local directory '/tmp/carbon3'
ROWFORM SERDE'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES  ('field.delim' = '\t', 'serialization.null.format' = 'NULL')
select * from carbon_test1;

The exported data is as follows:

111 NULL
222 NULL

The serialization.null.format parameter is the key, which is used to set the null value.

That's all, thanks!

  • x
  • convention:

olive.zhao
Admin Created Nov 15, 2021 09:24:38

Thanks for your sharing!
View more
  • x
  • convention:

user_4358465
Created Nov 21, 2021 15:40:10

Nice solution post...
View more
  • x
  • convention:

Comment

You need to log in to comment to the post Login | Register
Comment

Notice: To protect the legitimate rights and interests of you, the community, and third parties, do not release content that may bring legal risks to all parties, including but are not limited to the following:
  • Politically sensitive content
  • Content concerning pornography, gambling, and drug abuse
  • Content that may disclose or infringe upon others ' commercial secrets, intellectual properties, including trade marks, copyrights, and patents, and personal privacy
Do not share your account and password with others. All operations performed using your account will be regarded as your own actions and all consequences arising therefrom will be borne by you. For details, see " User Agreement."

My Followers

Login and enjoy all the member benefits

Login

Block
Are you sure to block this user?
Users on your blacklist cannot comment on your post,cannot mention you, cannot send you private messages.
Reminder
Please bind your phone number to obtain invitation bonus.
Information Protection Guide
Thanks for using Huawei Enterprise Support Community! We will help you learn how we collect, use, store and share your personal information and the rights you have in accordance with Privacy Policy and User Agreement.