hotamul의 개발 이야기

Sample DDL CREATE EXTERNAL WEB TABLE training.read_gpdb_log_D_1 ( event_time text, user_name text, database_name text, process_id text, thread_id text, remote_host text, remote_port text, session_start_time text, transaction_id text, gp_session_id text, gp_command_count text, gp_segment text, slice_idtext, distr_tranx_id text, local_tranx_id text, sub_tranx_id text, event_severity text, sql_stat..

위 와 같이 Greenplum Command Center에서 마지막 Vacuum, Analyze 시간을 확인 할 수 있다. Query에서 확인 pg_stat_all_tables View에서 last_vacuum, last_analyze 컬럼으로 확인 할 수 있다. -- example select pg_stat_all_tables sql SELECT schemaname, relname as tablename, last_vacuum, last_analyze FROM pg_stat_all_tables WHERE schemaname = 'training' ; --- example output schemaname | tablename | last_vacuum | last_analyze -------..

Database 실행 계획을 가독성 있는 그래프로 보는 방법은 아래 링크를 이용하면 됩니다! Postgres Explain visualizer Example -- 파티셔닝이 되어 있는 테이블 EXPLAIN (ANALYZE, FORMAT JSON) SELECT o_orderpriority, count(*) as order_count FROM orders WHERE o_orderdate >= date '1994-05-01' AND o_orderdate < date '1994-05-01' + interval '3 month' AND exists ( SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate ) GRO..

-- example returning deleted rawdata WITH deleted_rows AS ( DELETE FROM products WHERE "date" >= &#39;2010-10-01’ AND "date" < &#39;2010-11-01’ RETURNING * ) SELECT * FROM deleted_rows; 위 쿼리 처럼 WITH 구문을 이용하고 DELETE 문 다음 RETURNING * 사용하면 실제 delete된 데이터를 사용할 수 있어요😊 delete된 데이터를 다른 테이블에 insert 하거나 COPY 해두거나 한다면 유용하게 사용될 수 있을거에요!

Isolation level of Transactions In a select transaction, several problems might occur: Dirty Read: Other transactions can see changes from the current transaction, even if the changes are not yet committed Non-Repeatable Read: Repeated attempts to read the same data results in different result sets Phantom Read: By inserting, modifying or deleting data, other transactions will see different resu..

아래와 같은 External Table을 define 하거나 COPY operation을 실행할 때 발생한 에러를 확인하려면 gp_read_error_log 함수를 이용해 table 형태로 에러가 발생한 각각의 row에 대해 확인할 수 있어요. (Viewing Bad Rows in the Error Log) -- create external table example CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION (&#39;gpfdist://etlhost-1:8081/*&#39;, &#39;gpfdist://etlhost-2:8082/*&#39;) FORM..

SELECT n.nspname as schemaname, c.relname as tablename, pg_get_table_distributedby(c.oid) distributedby, case c.relstorage when &#39;a&#39; then &#39; append-optimized&#39; when &#39;c&#39; then &#39;column-oriented&#39; when &#39;h&#39; then &#39;heap&#39; when &#39;v&#39; then &#39;virtual&#39; when &#39;x&#39; then &#39;external table&#39; end as "data storage mode" FROM pg_class as c INNER J..

CLI $ gpcc start 2022-12-14 18:19:04 Starting the gpcc agents and webserver... 2022-12-14 18:19:08 Agent successfully started on 1/1 hosts 2022-12-14 18:19:08 View Greenplum Command Center at http://mdw:28080Web 접속 http://[GPDB IP 주소]:28080 으로 접속 default id: gpmon pw: changeme 참고: vmware - gpcc

Concurrency vs. parallelism Concurrency is about dealing with lots of things at once. Parallelism is about doing lots of things at once. Not the same, but related. Concurrency is about structure, parallelism is about execution. Concurrency provides a way to structure a solution to solve a problem that may (but not necessarily) be parallelizable. An analogy Concurrent: Mouse, keyboard, display, a..

Installation ❯ brew intstall go When installed, run go version to see the installed version of Go. ❯ go version go version go1.17.6 darwin/arm64 Setup worksapce The GOPATH environment variable specifies the location of your workspace. It defaults to a directory named go inside your home directory. ❯ go env GOPATH $HOME/go add GOPATH to your shell/bash/zsh initialization file .bash_profile, .bash..