On 07-12-2007 07:48:38 -0500, Venks wrote:
Hi,
Is there are way I can perform some SQL processing while using COPY to load the data?
For e.g. I need to load a file into a table but would like to TRIM all the spaces at the beginning and at the end of each column for all VARCHAR Columns. Below is an example in MySQL where I can load the data into a variable first and then load the variable into an actual column.
Maybe not what you expect/are looking for, but isn't this exactly what sed, awk, bash and many other tools were made for? You can plug them into the stream right before the data is fed to mclient.
LOAD DATA .... ( @col1_temp, @col2_temp, @col3_temp ) SET col1 = TRIM(@col1_temp), SET col2 = TRIM(@col2_temp), SET col3 = TRIM(@col3_temp)
@<variable> stores the initial value of each field and then you can use most of the default SQL functions on the variable before inserting the data.
The advantage here is the ability to perform some basic transformations on the data to adhere to the column definitions instead of loading into a temp table and then performing the transformations to load into the actual table. This really helps when loading billions of rows and 100G worth of data.