SAS Compute Server read-write Partitioned Parquet Data Files to AWS S3 Storage

The SAS Viya platform enables users to read and write Parquet files directly in AWS S3 using the SAS Parquet LIBNAME engine. When saving data from the SAS Compute Server to S3, you can partition the Parquet files for optimized storage and performance. You can also read the partitioned Parquet data files in S3 from the SAS Compute Server.

In this post, I discuss the SAS Compute Server reading and writing partitioned Parquet data files to S3 storage.

When saving SAS datasets to Amazon S3 as Parquet files, the SAS Compute Server temporarily buffers the data in memory before writing it to storage. For large tables, this memory consumption can impact resource availability for concurrent tasks, making environment-level memory management critical. SAS users can leverage the PARTITION_BY= and PARTITION_TYPE= dataset options to read and write partitioned Parquet files, with full support for HIVE, DIRECTORY, and FILENAME partitioning strategies.

There are a few rules for reading and writing partitioned parquet data files.

Rules for writing partitioned data to Parquet:

The target location must be empty.
The PARTITION_BY= data set option is required.
The PARTITION_TYPE= data set option is optional. The default for creating a table is HIVE.
To change a table's partitioning, you must re-create the table.

Rules for reading partitioned data from Parquet:

The PARTITION_TYPE= data set option is required. The value must be correct.
The PARTITION_BY= data set option is not required if you set the PARTITION_TYPE= data set option to HIVE.
If the partition type is DIRECTORY or FILENAME, the PARTITION_BY= option currently supports reading string columns only.

The following code demonstrates how to save a SAS dataset to Amazon S3 as partitioned Parquet files. The code demonstrates the usage of HIVE, DIRECTORY, and FILENAME partitioning strategies.

Code:

%let userid=gno087;
%let s3bucket=&userid.dmviya4 ;
%let aws_config_file="/mnt/viya-share/config/access-key/config"  ;
%let aws_credentials_file="/mnt/viya-share/config/access-key/credentials"  ;
%let aws_profile="default"  ;
%let aws_region="useast";

libname parqtlib parquet "/data/PARQUET/"
   storage_platform = "aws"
   storage_bucket_name = "&s3bucket"
   storage_aws_config_file=&aws_config_file
   storage_aws_key_file=&aws_credentials_file
   storage_aws_key_file_profile=&aws_profile
   storage_aws_region=&aws_region
   ;

data parqtlib.hive_example( partition_by =(League) partition_type=hive );
   set sashelp.baseball;
run;

data parqtlib.directory_example( partition_by =(League) partition_type=directory );
   set sashelp.baseball;
run;

data parqtlib.filename_example( partition_by =(League) partition_type=filename );
   set sashelp.baseball;
run;

Log:

81   %let userid=gno087;
82
83   %let s3bucket=&userid.dmviya4 ;
84   %let aws_config_file="/mnt/viya-share/config/access-key/config"  ;
85   %let aws_credentials_file="/mnt/viya-share/config/access-key/credentials"  ;
86   %let aws_profile="default"  ;
87   %let aws_region="useast";
88
89
90   libname parqtlib parquet "/data/PARQUET/"
91      storage_platform = "aws"
92      storage_bucket_name = "&s3bucket"
93      storage_aws_config_file=&aws_config_file
94      storage_aws_key_file=&aws_credentials_file
95      storage_aws_key_file_profile=&aws_profile
96      storage_aws_region=&aws_region
97      ;
NOTE: Libref PARQTLIB was successfully assigned as follows:
      Engine:        PARQUET
      Physical Name: /data/PARQUET/
98
99   data parqtlib.hive_example( partition_by =(League) partition_type=hive );
100     set sashelp.baseball;
101  run;
NOTE: There were 322 observations read from the data set SASHELP.BASEBALL.
NOTE: The data set PARQTLIB.hive_example has 322 observations and 24 variables.

103  data parqtlib.directory_example( partition_by =(League) partition_type=directory );
104     set sashelp.baseball;
105  run;
NOTE: There were 322 observations read from the data set SASHELP.BASEBALL.
NOTE: The data set PARQTLIB.directory_example has 322 observations and 24 variables.

106  data parqtlib.filename_example( partition_by =(League) partition_type=filename );
107     set sashelp.baseball;
108  run;
NOTE: There were 322 observations read from the data set SASHELP.BASEBALL.
NOTE: The data set PARQTLIB.filename_example has 322 observations and 24 variables.
109

The Parquet data files are stored in the S3 storage for HIVE, DIRECTORY, and FILENAME partitioning strategies.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

02_UK_SASViya_Computre_Server_ReadingWriting_Partitioned_ParquetFiles_To_S3_2.png

03_UK_SASViya_Computre_Server_ReadingWriting_Partitioned_ParquetFiles_To_S3_3.png

The following SAS code demonstrates how to read partitioned Parquet files from AWS S3 storage. The code demonstrates the usage of HIVE, DIRECTORY, and FILENAME partitioning strategies while reading respective partitioned data files. You can use a standard SAS data step or PROC SQL statement with PARTITION_BY= and PARTITION_TYPE= options to read the parquet partitioned data files. When a partitioned-based data filter is provided in the data step, or PROC SQL statement, only the corresponding data folder and files are read by the process, which helps the data query performance.

Code:

%let userid=gno087;
%let s3bucket=&userid.dmviya4 ;
%let aws_config_file="/mnt/viya-share/config/access-key/config"  ;
%let aws_credentials_file="/mnt/viya-share/config/access-key/credentials"  ;
%let aws_profile="default"  ;
%let aws_region="useast";

libname parqtlib parquet "/data/PARQUET/"
   storage_platform = "aws"
   storage_bucket_name = "&s3bucket"
   storage_aws_config_file=&aws_config_file
   storage_aws_key_file=&aws_credentials_file
   storage_aws_key_file_profile=&aws_profile
   storage_aws_region=&aws_region
   ;

data hive_baseball ;
   set parqtlib.hive_example( partition_by =(League) partition_type=hive );
   where League='American';
run;

data directory_baseball ;
   set parqtlib.directory_example( partition_by =(League) partition_type=directory );
   where League='National';
run;

data filename_baseball ;
   set parqtlib.filename_example( partition_by =(League) partition_type=filename );
   where League='National';
run;

Proc SQL outobs=5;
select * from parqtlib.hive_example( partition_by =(League) partition_type=hive )
where League='American';
run;quit;

Proc SQL outobs=5;
select * from parqtlib.directory_example( partition_by =(League) partition_type=directory )
where League='National';
run;quit;

Proc SQL outobs=5;
select * from parqtlib.filename_example( partition_by =(League) partition_type=filename )
where League='National';
run;quit;

Log:

81   %let userid=gno087;
82
83   %let s3bucket=&userid.dmviya4 ;
84   %let aws_config_file="/mnt/viya-share/config/access-key/config"  ;
85   %let aws_credentials_file="/mnt/viya-share/config/access-key/credentials"  ;
86   %let aws_profile="default"  ;
87   %let aws_region="useast";
88
89   libname parqtlib parquet "/data/PARQUET/"
90      storage_platform = "aws"
91      storage_bucket_name = "&s3bucket"
92      storage_aws_config_file=&aws_config_file
93      storage_aws_key_file=&aws_credentials_file
94      storage_aws_key_file_profile=&aws_profile
95      storage_aws_region=&aws_region
96      directories_as_data=YES
97      ;
NOTE: Libref PARQTLIB was successfully assigned as follows:
      Engine:        PARQUET
      Physical Name: /data/PARQUET/
98
99   data hive_baseball ;
100     set parqtlib.hive_example( partition_by =(League) partition_type=hive );
101     where League='American';
102  run;
NOTE: There were 175 observations read from the data set PARQTLIB.hive_example.
      WHERE League='American';
NOTE: The data set WORK.HIVE_BASEBALL has 175 observations and 24 variables.
103
104  data directory_baseball ;
105     set parqtlib.directory_example( partition_by =(League) partition_type=directory );
106     where League='National';
107  run;
NOTE: There were 147 observations read from the data set PARQTLIB.directory_example.
      WHERE League='National';
NOTE: The data set WORK.DIRECTORY_BASEBALL has 147 observations and 24 variables.
108
109  data filename_baseball ;
110     set parqtlib.filename_example( partition_by =(League) partition_type=filename );
111     where League='National';
112  run;
NOTE: There were 147 observations read from the data set PARQTLIB.filename_example.
      WHERE League='National';
NOTE: The data set WORK.FILENAME_BASEBALL has 147 observations and 24 variables.
114  Proc SQL outobs=5;
115  select * from parqtlib.hive_example( partition_by =(League) partition_type=hive )
116  where League='American';
WARNING: Statement terminated early due to OUTOBS=5 option.
117  run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
119  Proc SQL outobs=5;
120  select * from parqtlib.directory_example( partition_by =(League) partition_type=directory )
121  where League='National';
WARNING: Statement terminated early due to OUTOBS=5 option.
122  run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
124  Proc SQL outobs=5;
125  select * from parqtlib.filename_example( partition_by =(League) partition_type=filename )
126  where League='National';
WARNING: Statement terminated early due to OUTOBS=5 option.
127  run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

Results:

04_UK_SASViya_Computre_Server_ReadingWriting_Partitioned_ParquetFiles_To_S3_4.png