topic Reading Parquet file in Sas 9.4m6 locally in SAS Programming

Reading Parquet file in Sas 9.4m6 locally

vipinj765 — Fri, 11 Sep 2020 15:36:15 GMT

I have local downloaded copy of parquet file on linux server. Instead of connecting to hadoop cluster, i want to read the local version. Is there any way to do this in SAS 9.4m6 ? I do have Sas access to hadoop licensed

Re: Reading Parquet file in Sas 9.4m6 locally

ChrisNZ — Sat, 12 Sep 2020 03:05:09 GMT

Parquet is a binary compressed columnar data storage format. SAS has no means of reading this format directly; SAS can only do it via other applications such as Hive or Impala. This is similar to SAS not being a able to read a SQL Server file directly, it can only do so by using the SQL Server APIs to communicate with SQL Server.

Re: Reading Parquet file in Sas 9.4m6 locally

jimbarbour — Sat, 12 Sep 2020 03:18:56 GMT

How did you do the download? Did you download an HDFS file or did you do some kind of export procedure? If you just downloaded an HDFS file, you cannot read it directly with SAS.

However, if the file is small enough to download, why not just read the data using Proc SQL from within SAS and save the data as a SAS dataset? Typically a "Create Table As" (CTAS) style SQL query can be used for this.

For example:

PROC SQL NOPRINT;
  CREATE TABLE MyLib.SAS_Data_from_Hadoop AS
    SELECT * FROM Hdp.Some_Hadoop_Table;
QUIT;

You may need to qualify the Select with a Where of course, and you may need to apply Length and Format statements just as you would with any other SAS dataset.

The above technique, the CTAS style query, is usually the best way to work with data using SAS if you need a local copy of the data.

Jim

Re: Reading Parquet file in Sas 9.4m6 locally

vipinj765 — Mon, 14 Sep 2020 06:56:08 GMT

Hi Jim,

Thanks for your quick response

The file is a processed file with some ETL's done and then .parquet is created.The .parquet file lands on the landing zone which is connected to linux machine.

Within your give resolution you are trying to use HDP as a library which I think connects to Hadoop cluster.I dont want to connect to Hadoop cluster as I already have parquet file on my storage.

Thanks,Vipin

Re: Reading Parquet file in Sas 9.4m6 locally

vipinj765 — Mon, 14 Sep 2020 06:57:53 GMT

Thanks for your reply!

Re: Reading Parquet file in Sas 9.4m6 locally

ChrisNZ — Mon, 14 Sep 2020 06:59:48 GMT

> I dont want to connect to Hadoop cluster

You have no choice if you use SAS. SAS cannot read that file.

Re: Reading Parquet file in Sas 9.4m6 locally

LinusH — Mon, 14 Sep 2020 11:26:55 GMT

My interpretation is that the OP doesn't have a SAS/ACCESS to Hadoop licence, hence using SAS to make the copy would not be an (simple) option.

Work around would be to make an export to a flat file instead (of course the benefits of Parquet efficient storage is getting lost):

https://stackoverflow.com/questions/39419975/how-to-copy-and-convert-parquet-files-to-csv

Re: Reading Parquet file in Sas 9.4m6 locally

ChrisNZ — Tue, 15 Sep 2020 00:01:22 GMT

In OP: I do have Sas access to hadoop licensed

Good link though.

Re: Reading Parquet file in Sas 9.4m6 locally

LinusH — Wed, 16 Sep 2020 14:06:32 GMT

I jumped to a conclusion, and didn't read properly. Double sins!

But then it makes no sense why to read local copy wen you can access the Hadoop file directly...?

Re: Reading Parquet file in Sas 9.4m6 locally

jimbarbour — Wed, 16 Sep 2020 14:17:04 GMT

@LinusH,

If Hadoop's performance is slow, then it might make sense to have a local copy of some sort. However, HDFS data cannot be read by SAS, so it does not make sense, in my opinion, to just copy the HDFS file to one's local machine.

Some ideas that might make sense:

Use SAS and the Hadoop Libname engine to copy the Hadoop table into a local SAS dataset or table. This is the best option in terms of performance and convenience with SAS.
Export the HDFS data from Hadoop into a csv or other delimited file and then copy the csv file to one's local machine. I think this makes less sense because one then has to re-import the data into SAS, but this would at least work. Trying to read raw HDFS data locally without a local Hadoop instance will not work at all.

Jim