-1

My use case is as follows.

I have some SQL scripts which copy data from a CSV to a Vertica database. For every run, I want to add a special date to the rows.

Since I am starting those SQL scripts via an .sh file, I would try to do the following:

PATH/vsql -d *tablespace* -U *User* -w *Password* -f *SQLFILE.sql* $DATE

This works fine when I want to use the date parameter as an actual SQL parameter using &1.

What I actually want to do inside the SQL is the following:

copy *TABLENAME* (COL_1, COL2, $DATE) from local '*FILENAME.GZ*' gzip

How is it possible to use a value in a SQL file without passing it as a parameter?

EDIT: Sorry, I didn't figure out in the first place, how to edit.

To Specify the question: PATH/vsql -d *tablespace* -U *User* -w *Password* -f *SQLFILE.sql* $DATE Work if the sql looks like this: Select * from TABLE_NAME WHERE COLUMN_NAME = &1 So it is possible to pass a Parameter to the sql.

But I don't need it in the where clause, I want actually to add it in a copy statement:

copy *TABLENAME* (COL_1, COL2, $DATE) from local '*FILENAME.GZ*' gzip

I hope this makes the question more clear.

closed as unclear what you're asking by mustaccio, John aka hot2use, Colin 't Hart, John Eisbrener, Marcello Miorelli Nov 8 at 12:31

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Do you think you could update your question with the script that works (the one that accepts the date as a parameter)? That might be helpful. – Andriy M Nov 3 '16 at 18:45
  • The Script which is working looks like the following: PATH/vsql -d *tablespace* -U *User* -w *Password* -f *SQLFILE.sql* $DATE And in the SQL you can use the $Date Parameter like: Select * from *tablename* where columnname=&1 The &1 inserts the first parameter passed to the script. – Stub Nov 4 '16 at 7:14
  • Sorry, I wasn't clear. I meant to ask what your working SQL script (that uses a parameter) looks like. I also think that adding the target table's definition wouldn't hurt either. And please update the question rather than replying in comments. – Andriy M Nov 4 '16 at 8:48
0

You need to use a filler for this task. You add fillers for the existing data and increment the load with a new default value for the date column. See example here:

Take this table as examples

create table test_tbl(col1 int, col2 int, col3 int, datacol date);

And the data load is :

1,1,1
2,2,2
3,3,3

The copy command should be

  copy test_tbl(
   v1 FILLER int,
   v2 FILLER int,
   v3 FILLER int,
   col1 as v1,
   col2 as v2,
   col3 as v3,
   datacol as getdate()
   )
   from '/tmp/data.csv' delimiter ',' direct;

And the inserted data is :

dbadmin=> select * from test_tbl;
 col1 | col2 | col3 |  datacol
------+------+------+------------
    1 |    1 |    1 | 2016-11-04
    2 |    2 |    2 | 2016-11-04
    3 |    3 |    3 | 2016-11-04

More on Vertica Filler with some examples see here a post i made

  • Thanks this helps a bit! But how would it look like if the getdate() method shall be replaced by a parameter passed to the sql? – Stub Nov 4 '16 at 7:13
  • 1
    Why would you need that ? getdate() in vertica = date in OS – Up_One Nov 6 '16 at 23:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.