sql - Transposing and then exporting a table to a CSV file -

-

i have sql table 3 columns such:

enter image description here

i write script in amazon redshift (uses postgresql 8.0.2 believe) exports above table csv file transposed. transposed mean create new column each cobrand (there 4 distinct values in cobrand_id column) in csv file. illustrate want, included image (the values illustrative!):

enter image description here

when try:

 copy temp_08.jwn_calc 'p:/sql_new/products_199.csv' delimiter ',' csv header;

i error: [42601] error: syntax error @ or near "header" position: 74

when remove "csv header", error: [0a000] error: copy file xen-tables not supported

help appreciated!

transposing

to transpose data, you'll have write query names each column, such as:

select   qqyy "quarter",   sum(case when cobrand_id = 10001372 sum end) "10001372",   sum(case when cobrand_id = 10005244 sum end) "10005244",   sum(case when cobrand_id = 10005640 sum end) "10005640",   sum(case when cobrand_id = 10006164 sum end) "10006164" input_table group qqyy order qqyy

saving

the copy command in amazon redshift can load data from:

  • amazon s3
  • amazon dynamodb
  • an amazon emr cluster
  • a linux host running ssh

if wish load data redshift, should place csv (or zipped csv) amazon s3 bucket , use copy command import data.

if wish export data redshift, use unload command created zipped csv files in amazon s3. not possible directly download results redshift via unload command. alternatively, sql client runs locally on computer might have ability save query results file.

the error received due fact attempted access filesystem of redshift host computer (p:/sql_new/products_199.csv). not permitted, since have no login access host computer.

if have sql query transforms data want, use unload command export it:

unload ('select...from...') credentials ... 's3://my-bucket/output.csv'

Comments

Post a Comment

Popular posts from this blog

php - How to display all orders for a single product showing the most recent first? Woocommerce -

-
i have password protected page on frontend. on page, want display full list of purchases associated single woocommerce product id. option 1: i've looked through woocommerce docs , , found: wc_cli_order , list_( $args, $assoc_args ) function lists orders. presumably, orders can filtered product id [--=] filter orders based on order property. line item fields (numeric array, started index zero): line_items.0.product_id option 2: i found article , based on customer id. i've modified code based on guesses meta_key , meta_value. $customer_orders = get_posts( array( 'numberposts' => -1, 'meta_key' => '_product', 'meta_value' => get_product_id(), 'post_type' => wc_get_order_types(), 'post_status' => array_keys( wc_get_order_statuses() ), ) ); how display orders single product showing recent order first? here solution put based on these articles: http://www.w...
Read more

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

angularjs - How restrict admin panel using in backend laravel and admin panel on angular? -

-
https://scotch.io/tutorials/token-based-authentication-for-angularjs-and-laravel-apps following tutorials, try write authentication restrict admin panel, didn't understood how can that. this admin panel, admin panel don't want show , request database unauthenticated users. my laravel-routes route::get('/admin',function(){ return view('index'); }); route::get('/api/v1/employees/{id?}', 'employees@index'); route::get('/api/v1/users/{id?}', 'employees@users'); route::post('/api/v1/employees', 'employees@store'); route::post('/api/v1/employees/{id}', 'employees@update'); route::delete('/api/v1/employees/{id}', 'employees@destroy'); route::group(['prefix' => 'api'], function() { route::resource('authenticate', 'authenticatecontroller', ['only' => ['index']]); route::post('authenticate', 'authentic...
Read more