Skip to content

Exporting Data Warehouse Content to Parquet Files

The procedure described below results in producing Parquet files provided to researchers.

General Documentation about Exporting to Parquet

Please see Documentation for pg_export_parquet Module

Preparation

In preparation for exporting warehouse data to parquet files, here are the steps that we would need to do:

  1. Set up a job host - either make sure that a host is already running and will remain available, or submit a new job. Then, SSH into the host.
  2. Enter singularity shell
  3. Change to the directory where we will run export. (/shared/dorieh-logs)
  4. Make sure that the destination directory is writeable from the shell

Recommendation

If you are reattempting the export, delete everything from the existing directory. In theory, the export should overwrite existing files, but it is never hurt to be on the safe side. If for any reason some parquet files will be duplicated, some records will be duplicated as Parquet does not check for unique keys.

These setup steps mirror those used during data ingestion, including job host reservation, starting a Singularity shell, and confirming file system access.

For convenience, the corresponding commands are listed below:

sbatch sleeping_job.sh   
squeue  
ssh dominici-dorieh-st-dominici-dorieh-cr-0-1 # or whatever the worker node name is
singularity shell --bind /shared/home/${USER}/mnt/:/home/dorieh --bind /data --bind /shared/dorieh-logs/ /shared/software/singularity/dorieh/urcds-dorieh_latest.sif
cd /shared/dorieh-logs/export

Then we will need to run the following commands (adjusting -o ... option). The destination directory should be accessible from the running node. It will be created automatically if it does not exist.

These export commands can be run in parallel, but each one requires a separate worker node. Submit a separate SLURM job for each export process and monitor outputs independently. Currently, the partition is limited to 2 worker nodes.

Concurrent export of multiple tables

If running concurrently, it is best to run export of enrollments and admissions on separate worker nodes.

Running export

nohup python -u -m dorieh.platform.util.pg_export_parquet --db /home/dorieh/database.ini --connection dorieh --table medicare.beneficiaries -o /data/labs/dominici_nsaph_l3/parquet_dw/dorieh/medicare/beneficiaries 2>&1 > b_export.log &
nohup python -u -m dorieh.platform.util.pg_export_parquet --db /home/dorieh/database.ini --connection dorieh --table medicare.enrollments -o /data/labs/dominici_nsaph_l3/parquet_dw/dorieh/medicare/enrollments -p "year" --hard 2>&1 > e_export.log &
nohup python -u -m dorieh.platform.util.pg_export_parquet --db /home/dorieh/database.ini --connection dorieh --table medicare.admissions -o /data/labs/dominici_nsaph_l3/parquet_dw/dorieh/medicare/admissions -p "year"  --hard --sql " WHERE discharge_date > '1900-01-01'" 2>&1 > a_export.log &

--hard Option

The --hard option is essential for large tables like enrollments and admissions, as it works around a known memory leak in the PyArrow library.

--sql Option

For exporting admissions we are also adding a condition: --sql " WHERE discharge_date > '1900-01-01'" to work around invalid discharge dates.