Greenplum Archives - Page 2 of 2

Greenplum Computed Column Support and Alternative

Derived or computed columns in Greenplum are columns that are derived from the previously computed columns in same table. These computed columns are virtual columns that are not physically stored in the Greenplum table. Their values are re-calculated every time they are referenced in a query. Many relational databases such as Netezza supports derived or computed columns but Greenplum does not support as of now. Development work is going on to include this feature in upcoming ProsgreSQL release. In this article, we will check Greenplum Computed Column Support and alternative…

Comments Off

March 22, 2018

Greenplum

Greenplum Analyze and Examples

The most important prerequisites for good query performance is to collect the table statistics time to time using Greenplum analyze command. Greenplum analyze collects statistics about the contents of tables in the database, and stores the results in the system catalog table pg_statistic. Greenplum database uses these statistics to determine the best execution plan for the queries. Syntax: ANALYZE [VERBOSE] [ROOTPARTITION [ALL] ] [table [ (column [, ...] ) ]] Where: ROOTPARTITION [ALL]: Collect statistics only on the root partition of partitioned tables. VERBOSE: Enables display of progress messages. Table:…

Comments Off

September 5, 2016

Greenplum

Greenplum Encryption Options and Best Practices

To minimize the data breaches, now a day’s companies are increasingly adding security and cryptographic functions to their data at rest. This applies to the most of the big data appliances such as Greenplum, Netezza, Redshift etc. In this post we will see how the Greenplum encryption works. Greenplum support the data encryption at various level: Encrypting the Connections to the Database Encryption of data in Transit Encryption of data at Rest Database Connections Encryption In the Greenplum systems, connections between clients and the master database can be encrypted with SSL. This…

Comments Off

September 1, 2016

Greenplum

Built-in Greenplum Analytics Functions and Examples

Window functions or Greenplum analytics functions compute an aggregated value that is based on a group of rows. These functions allow the application developers to more easily write complex online analytical processing (OLAP) queries using standard SQL commands. For example, with Greenplum analytics functions or windows expressions, users can calculate moving averages or sums over various intervals, ranks as selected column values etc. Read: Greenplum Computed Column Support and Alternative Greenplum Architecture Greeplum Analytic Functions Examples Here are the examples of some commonly used Greenplum analytics functions: COUNT Analytics functions…

Comments Off

August 27, 2016

Greenplum

Greenplum Interview Questions and Answers – Part1

Explain Greenplum Architecture. Read Post: Greenplum Architecture How data is distributed using hash algorithm? Read Post : How Greenplum Hash Distribution Works What are different ways to get data into Greenplum data warehouse? COPY FROM Gpload INSERT statement Create EXTERNAL TABLE Explain how data is stored in Greenplu? Data is stored based on selected field (s) which are used for distribution. When you have a Distribution Key by Hash the values of the Distribution Key are run through a Hash Formula. Then, a map is used to distribute the row to the…

4 Comments

August 23, 2016

Greenplum

Greenplum Constraints:Table and Column Constraints

Greenplum Constraints are used to apply business rules for the database tables. You can define constraints on columns and tables to restrict the data in your tables. Greenplum Database support for constraints is the same as PostgreSQL with some limitations. Read: Greenplum Sequence and its Usage Greenplum Data Loading Options Greenplum constraints includes: CHECK NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Greenplum Constraints and Example CHECK Greenplum Constraints allows you to specify that the value in a certain column must satisfy a Boolean expression. The boolean condition will evaluate to…

Comments Off

August 21, 2016

Greenplum

Greenplum Sequence and its Usage

Like any other data warehouse appliances, Greenplum has sequences. Greenplum sequence is an auto number generator. These sequence then can be used in any SQL statements. Greenplum Sequence Overview CREATE SEQUENCE creates a new sequence number generator. This command willalso creates the special single-row table and initialize it. Sequence will be owned by the user creating it. Read: Greenplum Data Loading Sequence Also check: Greenplum Unloading Data Syntax: CREATE SEQUENCE name [Options] Following are the options associated with Greenplum sequence. [INCREMENT [BY] value] [MINVALUE minvalue | NO MINVALUE] [MAXVALUE maxvalue…

Comments Off

August 20, 2016

Greenplum

Greenplum Unloading Data Examples

Greenplum can supports unloading large amounts of data. It also supports single file, non-parallel export for small amounts of data. Greenplum unloading data is supported by various methods such as COPY TO command and create writable external tables. In this post you will learn how to unload data from Greenplum Database using parallel unload (writable external tables) and non-parallel unload (COPY) Read: Greenplum Data Loading Options How to Export Vertica Query Results into CSV? Create Writable External Tables Greenplum writable external table uses the Greenplum distributed file server, gpfdist to create file…

Comments Off

August 20, 2016

Greenplum

Greenplum Data Loading Options

Being a MPP server, Greenplum supports parallel data loading for large amounts of data. It also supports single file, non-parallel import for small amounts of data. Greenplum data loading is supported by various methods as follows. Read: Greenplum Architecture Data Loading Options Greenplum supports following tools for loading 1. Greenplum data Loading with gpload Command The gpload Greenplum data loading utility is an interface to external table parallel loading feature. gpload uses a load specification or layout defined in a YAML formatted control file to load data into the target table…

Comments Off

August 19, 2016

Greenplum

Greenplum Architecture

Like IBM Netezza and Amazon Redshift, Greenplum database is a massively parallel processing (MPP) database server. Greenplum architecture is designed to manage large scale data warehouse for analytics and business intelligence needs. Like any other large scale data warehouse appliances, Greenplum works well with Dimensional modeling. Read: Star Schema Model in Data Warehouse Step By Step Guide to Dimensional Modeling Greenplum Architecture Overview The MPP environment shared nothing architecture is made up of two or more processor that work together to perform tasks. Each processor has its own memory, operation…

Comments Off

August 19, 2016