Loading data via COPY to a table without ksafty.

Moderator: NorbertKrupa

Post Reply
fane89
Newbie
Newbie
Posts: 6
Joined: Tue Jul 25, 2017 9:12 am

Loading data via COPY to a table without ksafty.

Post by fane89 » Tue Jul 25, 2017 9:38 am

Hello,

I've search the forum, but could not find any solutions, please point me in the right direction if i missed something.

Question:
Is there a way to load data via copy to a table with ksafty=0 (when global ksafty is not 0)?
Loading to a temporary table is not a solution for me, i need the data in the table between different sessions.

Basically I'm asking for a way to create a non-temporary table with ksafty=0 when global ksafty is not 0.
One way would be to set global ksafty=0 and then only on important tables set ksafty to 1 or 2, but this smells like trouble.

Any ideas?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1755
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Loading data via COPY to a table without ksafty.

Post by JimKnicely » Tue Jul 25, 2017 8:44 pm

Hi,

As far as I know, you cannot create a permanent table that has a KSAFE value less than the system KSAFE value ...

dbadmin=> select current_fault_tolerance from system;
current_fault_tolerance
-------------------------
1
(1 row)

dbadmin=> create table jim (c1 int) ksafe 0;
ERROR 4478: Projection KSAFE override 0 cannot be less than current system K-safe value 1

dbadmin=> select node_name from nodes;
node_name
-----------------
v_test_node0001
v_test_node0002
v_test_node0003
(3 rows)

dbadmin=> create table jim (c1 int) segmented by hash(c1) nodes v_test_node0001;
ERROR 4477: Projection KSAFE 1 can not be met with only 1 nodes


Why would you want to? It's counter intuitive to the whole point of K-SAFETY.
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

fane89
Newbie
Newbie
Posts: 6
Joined: Tue Jul 25, 2017 9:12 am

Re: Loading data via COPY to a table without ksafty.

Post by fane89 » Wed Jul 26, 2017 12:43 pm

Hi,

I'm trying to optimize the COPY process.
when loading data via COPY (using direct) to a table with ksafty=0 times are faster, then loading to table with ksafty=1 or 2.

The perfect solution would be to load to a temporary table, but i need access to data between sessions.

For example:
Loading 10GB csv via COPY to a single node env (i know its not recommended to have just 1 node) is faster then loading it to a 3 node env.

Am I right assuming that i have to trade off perfomance for security when using ksafty 1 or 2 ?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1755
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Loading data via COPY to a table without ksafty.

Post by JimKnicely » Thu Jul 27, 2017 2:44 pm

Vertica can load data extremely fast! Instead of doing a single COPY command, make sure you do things in parallel:

https://my.vertica.com/docs/8.1.x/HTML/ ... treams.htm
https://my.vertica.com/kb/Data-Loading- ... g-COPY.htm
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

fane89
Newbie
Newbie
Posts: 6
Joined: Tue Jul 25, 2017 9:12 am

Re: Loading data via COPY to a table without ksafty.

Post by fane89 » Thu Aug 03, 2017 10:16 am

Thanks, i will look into that.

Post Reply

Return to “Vertica Data Load”