Greenplum external table single column of data

Had to work with some data that was one column of data in a fixed width format. Solution was to create an external table that was one column then use substr function to pull out the data.

External table:

CREATE EXTERNAL TABLE trmtmp_ext_table (
    col1 text
)
LOCATION (
        'gpfdist://host-1:port/path/trmtmp.out',
        'gpfdist://host-2:port/path/trmtmp.out',
        'gpfdist://host-3:port/path/trmtmp.out'
)
FORMAT 'text'
LOG ERRORS INTO trmtmp_err_ext_table SEGMENT REJECT LIMIT 5 ROWS;

View of the external table:

DROP VIEW trmtmp_view_ext_tbl;
CREATE VIEW trmtmp_view_ext_tbl AS (
SELECT
    substr(col1,1,2) AS col1,
    substr(col1,3,5) AS col2
FROM  
    trmtmp_ext_table
);

Extrapolate from there.

Comments

Post a Comment

Popular posts from this blog

Database, schema, and table sizes in Greenplum

Starting in the 4.x release, you get size info from the gp_toolkit schema. To get the size of all databases and their size in bytes: select sodddatname, sodddatasize from gp_toolkit.gp_size_database; To see the database size in GB, TB, and/or MB. TB: select sodddatname, (sodddatsize/1073741824)/1024 AS sizeinTB from gp_toolkit.gp_size_of_database; GB: select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database; MB: select sodddatname, (sodddatsize/1048576) AS sizeinMB from gp_toolkit.gp_size_of_database; For schema sizes , connect to your database and run: TB: select sosdnsp, (sosdschematablesize/1073741824)/1024 AS schemasizeinTB from gp_toolkit.gp_size_of_schema_disk; GB: select sosdnsp, (sosdschematablesize/1073741824) AS schemasizeinGB from gp_toolkit.gp_size_of_schema_disk; MB: select sosdnsp, (sosdschematablesize/1048576) AS schemasizeinMB from gp_toolkit.gp_size_of_schema_disk; If you want a specific schema only, add
Read more

Greenplum update with multiple tables

Recently ran into an issue using Greenplum to update a column. I had column1 in tableA that needed to be set to the value of column1 in tableB. Finally came across the correct syntax here . This is what I was looking for: UPDATE tableA  SET col1 = tableB.col1  FROM tableB  WHERE tableA.col2=tableB.col2;
Read more

Show running queries on Postgresql/Greenplum

I've got some queries that run for a long time. Sometimes longer than others. I wanted to see how long active queries have been running. Here's a view to consistently do that: CREATE VIEW public.view_activequeries AS ( SELECT age(query_start, backend_start) AS queryage,* FROM pg_stat_activity WHERE current_query NOT LIKE '%IDLE%' ORDER BY queryage DESC); Then you just run: select * from public.view_activequeries; You get a list of all active queries in descending order of how long they have been running, i.e. longest running queries listed first.
Read more