SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

Reply
Regular Contributor
Posts: 182

SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

Hi,

 

I have a Postgres table with a serial column (autoincrement) and want to append new data to it using the DI Studio table loader.

 

In the Table Loader I leave the column mapping empty for the serial column (__screening_id) hoping that it will not be loaded so that Postgres can fill it in itself:

2016-11-04_17-57-36.png

But that is not what is happening.

It generates a mapping for __screening_id as follows:

 

 

  %put %str(NOTE: Mapping columns ...);
   proc sql;
      create view work.W5CY70T as
         select
            . as "__screening_id"n length = 8,
            "__screen_id"n,
            "__screening_job_run_id"n,
            "__screening_object"n,
            "__screening_dts"n,
            "__screening_result"n,
            "__screening_msg"n,
            "" as "__update_user"n length = 32
               format = $32.
               informat = $32.,
            . as "__update_dts"n length = 8
               format = DATETIME25.6
               informat = DATETIME25.6
      from &etls_lastTable
      ;
   quit;

 

 

and then a proc append. This returns the following Postgres error:

 

 

ERROR: During insert: ERROR: null value in column "__screening_id" violates not-null constraint 
Failing row contains (null, 5, null, WIKSTO.TAG_MEASURES_NUM_PI, 2016-11-04 17:18:00, PASSED, 0 rows passed, 0 rows failed, 0 screens passed, 0 screens failed, null, null).; Error while executing the query

How can I make this work?

 

Bart

 

 

Respected Advisor
Posts: 3,892

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

I believe there is no option where you can exclude a target column from being part of the SQL, not sure though and may be there is somewhere a drop option.

 

What should work is to have a "duplicate" table metadata object for loading where you remove your auto increment column.

 

Super User
Posts: 5,256

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

Try IGNORE_READ_ONLY_COLUMNS=YES libname option.
Data never sleeps
Regular Contributor
Posts: 182

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

I tested a duplicate metadata registration of the Postgres table without the SERIAL column (__screening_id).

This does not help (with proc append anyway), behind the scenes SAS queries the target table and generates Postgres SQL insert code for all columns:

 

 

POSTGRES_49: Prepared: on connection 4
INSERT INTO "DQ_SCREENINGS"
("__screening_id","__screen_id","__screening_job_run_id","__screening_object","__screening_dts","__screening_result","__screenin
g_msg","__update_user","__update_dts")  VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? )

 

I tested IGNORE_READ_ONLY_COLUMNS=YES, which also did not work.

It looks like SERIAL data type in Postgres is not read-only and so the libname engine does not recognize it as such.

 

What I found does work is specifying the (drop=serial_column) table option on the output dataset:

2016-11-06_01-04-49.png

The generated code is then:

 

 

   proc append base = WIKCNTL."DQ_SCREENINGS"n(drop=__screening_id)
      data = &etls_lastTable (&etls_tableOptions)  force ;
    run;

 

 

But then this gives a warning (unacceptable in our batch) and I can't find a way to have the table loader add the NOWARN option to PROC APPEND.

Any (other) ideas?

Respected Advisor
Posts: 3,892

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

[ Edited ]

I believe things could work if you use "Insert" instead of "Append" together with the approach of duplicate table metadata registration of the Postgres table without the SERIAL column.

 

Capture.PNG

 

Still worth trying what @LinusH proposed and I'd be very curious if that works.

- just read that you've tried this already.

Regular Contributor
Posts: 182

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

I don't like the duplicate metadata registration.

 

I built my own (very limited) table loader transformation to:

- skip unmapped columns

- optionally add the NOWARN option to PROC APPEND

 

For what it's worth, the code I currently have (which will surely evolve when I find errors in it and need additional options).

 

%macro x;
 
  %let rc = 0;
  %if (&loadstyle = REPLACE_DROP) %then
  %do;
       %if (&_OUTPUT_engine. = SAS) %then
       %do;
            proc sql;
              drop table &_OUTPUT.;
            quit;
       %end;
       %else
       %do;
            %put ERROR: REPLACE_DROP is only allowed for SAS data sets.;
            %let rc = 4;
       %end;
  %end;
  %if (&loadstyle = REPLACE_DELETE) %then
  %do;
       proc sql;
         delete * from &_OUTPUT.;
       quit;
       %let rc = &sqlrc;
  %end;
  %if (&loadstyle = REPLACE_TRUNCATE) %then
  %do;
       %if (&_OUTPUT_engine. = POSTGRES) %then
       %do;
            data _null_;
              call symput('postgres_table',strip(scan(scan(symget('_OUTPUT'),2,'.'),1,'"')));
            run;
            %put NOTE: &=POSTGRES_TABLE;
            proc sql;
              connect to postgres (&_OUTPUT_connect);
              execute ( truncate "&POSTGRES_TABLE" ) by postgres;
              disconnect from postgres;
            quit;
            %let rc = &sqlrc;
       %end;
       %else
       %do;
            %put ERROR: REPLACE_TRUNCATE is only allowed for Postgres tables.;
            %let rc = 4;
       %end;
  %end;
  %if (&rc = 0) %then
  %do;
 
       proc datasets lib=WORK nolist nowarn;
         delete __LOAD;
       run;
       proc sql;
         create view WORK.__LOAD as
           select
           %let first = 1;
           %do i=0 %to %eval(&_OUTPUT_col_count. - 1);
                %if ((&&&_OUTPUT0_col&i._exp. ne ) or (&&&_OUTPUT0_col&i._input_count. > 0)) %then
                %do;
                     %if (&first. ne 1) %then , ;
                     %else %let first = 0;
                     %if       (&&&_OUTPUT0_col&i._exp.         ne ) %then &&&_OUTPUT_col&i._exp;
                     %else %if (&&&_OUTPUT0_col&i._input_count. > 0) %then &&&_OUTPUT_col&i._input;
                     as &&&_OUTPUT_col&i._name
                     %if (&&&_OUTPUT_col&i._type.     =  $) %then length=&&&_OUTPUT_col&i._length.;
                     %if (&&&_OUTPUT_col&i._format.   ne  ) %then format=&&&_OUTPUT_col&i._format.;
                     %if (&&&_OUTPUT_col&i._informat. ne  ) %then informat=&&&_OUTPUT_col&i._informat.;
                     %if (&&&_OUTPUT_col&i._label.    ne  ) %then label="&&&_OUTPUT_col&i._label.";
                %end;
           %end;
           from   &_INPUT
         ;
       quit;
       proc append base=&_OUTPUT.(
           %let first = 1;
           %do i=0 %to %eval(&_OUTPUT_col_count. - 1);
                %if ((&&&_OUTPUT0_col&i._exp. eq ) and (&&&_OUTPUT0_col&i._input_count. = 0)) %then
                %do;
                     %if (&first. = 1) %then
                     %do;
                          drop =
                          %let first = 0;
                     %end;
                     &&&_OUTPUT_col&i._name
                %end;
           %end;
                   )
                   data=WORK.__LOAD
                   %if (%upcase(&warn) = NOWARN) %then NOWARN;
                   ;
       run;
  %end;
%mend x;
options mprint;
%x;
Super User
Posts: 5,256

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

 
Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 407 views
  • 0 likes
  • 3 in conversation