Technology – When To Cache A Denodo View

Advertisements

Here’s a quick summary of practices about when to use cache when developing denotative views.  These guidelines come from the usual documentation and practical experience and may help you decide whether to cache a view. These are general guidelines, and they should happen the conflict with any guidance you’ve gotten from the Denodo; Please use the advice provided by Denodo.

What is a table cache?

In denodo, a cache is a database table that contains a result set of a view at the point in time, which is stored in a JDBC database

Why Cache?

Cache in Denodo can be used for several purposes:

Enhancing Performance

Improving performance is the primary purpose of caching and can be overcome slow data sources, data sources with limited SQL functionality, and/or tuned long runner views. 

Protecting data sources from costly queries

Caching can shield essential systems from excess load cause by query load from large, long-running queries and/or frequent queries during critical operation times.

Reusing complex data combinations and transformations

Caching views that consolidate data from multiple data sources, perform complex calculations, and apply complex derivations and business rules provide and optimize pre-enriched data set for consumption.

Cache View Modeling Best Practice

Add a primary key or a unique index

Adding a primary key or a unique index helps the optimizer define performance strategies and accurate cost estimates when the view joins to other views.

Add Cache indexes

Add Cache indexes based on understanding actual consumer usage of view (e.g., commonly used prompts, etc.)

Caching Tips and Cautions

Here are some considerations to keep in mind when making caching decisions.

Avoid Caching Intermediate Views

Where possible, avoiding caching of intermediate views allows the optimizer to make better decisions about data movement, pushdown, and branching.  This allows denodo to perform great SQL simplification. 

The volume of view to be cached

Where possible, avoid caching large views (e.g., views with a large number of rows/columns). Evaluate the cache size and make an appropriate decision.

Denodo Reference Links

Best Practices to Maximize Performance III: Caching

Denodo E-books

Denodo Cookbook: Query Optimization

Related Blog Posts

Denodo View Performance Best Practice

Technology – Denodo Supported Business Intelligence (BI) and Reporting Tools

Advertisements

The question of which PI tools to Denodo supports comes up perhaps more often than it should. The question usually comes in the form of a specific intelligence (BI) and reporting tool being asked about. For example, does Denodo support tableau or Cognos, etc.

Denodo does provide a list of intelligence (BI) and reporting tools that they support. However, the list of the most commonly used intelligence (BI) and reporting tools. And there is a reason for that, which, basically, boils down to whether or not the intelligence (BI) and reporting tools can use ODBC or has a JDBC driver.  So, even if it’s not on Denodo’s list doesn’t mean you can’t use the tool. , it may just mean that the software may not be one of the most frequently used.

Simple List Of Business Intelligence (BI) And Reporting Tools Supported By Denodo.

Here is a simple list of the tools which Donato has provided on their knowledgebase page. So, I strongly recommend you visit the page and for additional details and software specific documentation links.

  • Alteryx
  • IBM Cognos
  • Informatica Power Center
  • Looker
  • Microsoft SQL Server Reporting Services (SSRS)
  • Microstrategy
  • OBIEE
  • Pentaho
  • Power BI Desktop
  • Qlik
  • SAP Business Objects
  • SAP Lumira
  • Splunk
  • Tableau
  • Tibco Spotfire

Finding the Denodo page that lists these commonly use business intelligence (BI) and reporting tools sometimes causes issues.  Because they discuss it in terms of northbound, which is typical for them, but not the way other people think about it.

I have provided a link to the Denodo list of supported ODBC and JDBC business intelligence (BI) and reporting tools.  Hopefully, this post will make it a little easier for you to find the Denodo list of supported ODBC and JDBC business intelligence (BI) and reporting tools.

Denodo Reference Links

Denodo > Knowledge Base > Northbound Connections > Denodo and BI Tools

Technology – Denodo VQL To Get A List Of Cached View Names

Advertisements

Hello, this is a quick code snippet of a Denodo VQL (Denodo Virtual Query Language) to pull a list of cached view names which can be useful in pulling list of cached views. It’s not a complicated thing, but now that I’ve bothered to look it up on putting this note here mostly for me but you may find useful. I have found this useful for several reasons not the least of which is for creating jobs to do maintenance of cached view statistics.

Example VQL List Of Cached View Names

select name view_name

from get_views()

       where cache_status <> 0

       and database_name = ‘uncertified’

       and name not like ‘%backup’

       and name not like ‘%copy’

       and name not like ‘%test’

       and name <> ‘dv_indexes’;

Denodo Reference Links

·         Denodo > Denodo Platform 8.0 > User Manuals > Virtual DataPort VQL Guide > Stored Procedures > Predefined Stored Procedures > GET_VIEWS

Technology – Denodo View Performance Best Practice

Advertisements

Since I have been doing more training of beginning users of the Denodo, there have been a lot of questions around performance best practices and optimization. This article is a quick summary of some of the high points of the Donodo documentation, which are typically useful.

However, I would like to point out that the performance of Denodo views is:

  • usually, an ongoing process as your environment evolves and that your code changes
  • also, the performance of Denodo views may involve elements be on the Denodo framework itself, such as source system databases
  • and may require some administration configuration and reengineering to achieve your full benefits in terms of establishing environment sizing, data movement databases, use of bulk load processes, and maintenance processes (E. G., Scheduled index maintenance, scheduled statistics maintenance)
  • furthermore, good general SQL and coding practices have a great deal to do with performance unrelated to the denotative toolset.

Avoid ‘Create View From Query’

Using ‘Create view from Query’ to create base views bypasses the denodo optimization engine and pushes directly to the data source as written.

Make Sure Primary Keys (PK) And Unique Indexes Have Been Set

Accurately setting the primary key (PK) on views (especially, base views): aides:

  • The static optimization phase, primary keys, and unique indexes enable Join pruning and Aggregation push-down when appropriate
  • The Primary Key is presented to consuming applications and RESTFUL web services
  • Allow browsing across the associations of views with Data Catalog.

Mirror Source System Indexes

Aiding source database indexes to denodo base views aids the denodo optimizer to make appropriate decisions. However, avoid adding indexes on the table which do not exist in the source database. This will cause the optimizer to make incorrect execution plans and will undercut performance.

Note: Primary Keys (PK) are enforced by denodo, only used to enable optimization and application capabilities.

Apply Foreign Key (FK) And Referential Constraint Associations

An association represents a foreign key relation.  However, when a referential constraint is applied to an association, every row of the ‘Dependent’ view has a matching value in the ‘Principal’ view, which meets the Condition mapping. 

Adding Indexes To Cache Views

Adding Primary Keys and Unique indexes to cached tables also aids the optimizer and, if properly maintained, aids normal database operation when querying cache tables.

Gather and Maintain View Statistics

View statistics play an essential role, helping the optimizer make decisions about execution plans and data movement.  Statistics are most important for base views and cached, especially the total rows, average size, and distinct view values.

Caching Derived Views

Caching large, long-running, complex views can improve performance and limit source system impacts and Denodo if the cache guidelines are followed.  However, to optimize efficient cached views should have Primary Keys, Unique Indexes, performance indexes, and Statistics. See caching Guidelines for additional detail.

Use Effective Joins

Effective joins are essential to performant view.  Here are some high-level tips to keep in mind when building joins:

  • When possible, use simple join condition
  • Join on primary keys or unique indexes
  • Leverage Foreign Key (FK) and Primary Keys (PK).  Especially when an association referential constraint is defined
  • Use Inner joins when Possible
  • When using outer joins, Organize joins by data source when using multiple data sources
  • Avoid using view parameters and subqueries on the join condition

Use A Building Block Approach

Breaking views into discreet units allows the optimizer more opportuning to optimize SQL’s and performance.  Here are a few tips for using the building block approach:

  • Create views for different entities (Fact, Dimension, or Subject set)
  • Build views for discreet and/or distinct data subsets
  • Use SQL tuning rules to arrive at the smallest result set as soon as possible
  • Tune each view individually

Let Denodo Determine Optimal Data Movement

Where possible, avoid manually assigning data movement strategy.  Letting Denodo determine the optimal data movement strategy (assume other view optimizations have been applied) provides the greatest flexibility as the data changes across time.  When precursor views are updated and/or tuned and Prevents errors due to data movement strategy conflicts.

Denodo Reference Links

Best Practices to Maximize Performance I: Modeling Big Data and Analytics Use Cases

Best Practices to Maximize Performance II: Configuring the Query Optimizer

Best Practices to Maximize Performance III: Caching

Best Practices to Maximize Performance IV: Detecting Bottlenecks in a Query

Denodo Knowledge Base > Performance & Optimization

Denodo Knowledge Base > Performance

Denodo E-books

Denodo Cookbook: Query Optimization

Introducing DBVisualizer

Advertisements

It is difficult for most businesses to effectively use numerous data of information since enterprise data analysis and management is becoming more difficult and complex. With the growing chances of failure and higher stakes at risk, businesses need to choose the proper software application or software tool that will extract insights from the inside information and manage the database of their enterprise.

What is DBVisualizer?

DBVisualizer is designed as a universal database tool to be used by data analysts, database administrators, and software developers. This software application offers a straightforward and all-in-one UI or user interface for enterprise database management. It comes in both a paid professional edition that provides a wider variation of features and a free edition.

Is DBVisualizer an open-source application?

No, it is a proprietary software application.

Will DBVisualizer run on both Linux and Windows?

DBVisualizer is also dubbed as the universal database tool. It implies that it is capable of running on all of the major operating systems. Hence, the DBVisualizer SQL editor runs smoothly on Windows, Linux/UNIX, and macOS.

Which technical roles would use DBVisualizer most?

Technical roles that deal with databases regularly such as database administrators, developers, and analysts require specific aspects that can be of help to make their work easier. With DBVisualizer, developers can access the advanced DBVisualizer SQL editor that includes smart features that are needed in writing queries, avoiding errors, and speeding up the coding process. For analysts, it will be easier and quicker for them to understand and access the data with the insight feature. They can also easily manage and create the database visually. Lastly, database administrators can be assured that data is secured and preserved during sessions with the autosave feature of DBVisualizer. The software application is also highly optimized and customized to fit the workflow of the user.

Databases or databases types that the DBVisualizer supports

  • Db2
  • Exasol
  • Derby
  • Amazon Redshift
  • Informix
  • H2
  • Mimer SQL
  • MariaDB
  • Microsoft SQL Server
  • MySQL
  • Netezza
  • Oracle
  • SAP ASE
  • PostgreSQL
  • NuoDB
  • Snowflake
  • SQLite
  • Vertica
  • IBM DB2 LUW

Databases that are accessible with JDBC (Java Database Connectivity) driver is capable of working or running with DBVisualizer. You can also see DBVisualizer’s official website that some users have successfully used the software with other non-official database systems such as IBM DB2 iSeries, Firebird, Teradata, and Hive. Aside from that, you can also see the list of other databases that will soon be supported by DBVisualizer.

What are the most essential DBVisualizer documentation links?

Here are the following links that can cover the basic downloads to the application and basic information.

DBVisualizer Site

Installer download link for macOS, Windows 64-bit, Windows 32-bit, Linux, and Unix:

DbVisualizer Users Guide

List of features for free and pro version:

Introducing SQuirreL SQL

Advertisements

The business landscape of today is controlled and influenced by big data and it is also getting bigger and bigger as time goes by. Since the amount of data that is needed to be stored and organized is massive, data workers use SQL to access the information in a relational database. Software applications such as SQL clients can let users create SQL queries, access the database’s information, and view the models of relational databases. One of the most famous and sought out option for SQL clients is the SQuirreL SQL Client.

What is SQuirreL SQL?

It is a client for examining and retrieving SQL databases via a user-friendly and simple graphical user interface (GUI). It can run on any computer that has a Java Virtual Machine (JVM) since SQuirreL SQL is a programming language written in Java. You can download the SQuirreL SQL editor for free and is available in different languages such as English, Chinese, German, Russian, Portuguese, French, and Spanish.

Which technical roles would use SQuirreL SQL most?

SQuirreL SQL is useful and convenient for anyone who works on SQL databases regularly such as software developers, database administrators, application administrators, software testers, etc. For application administrators, they can use SQuirreL SQL to fix a bug at the level of the database. Aside from that, correcting and scanning for incorrect values in a table is easy using SQuirreL SQL. It can also help database administrators in overseeing huge varieties of relational databases, checking problems in tables, manage databases using commands, and viewing metadata.

Is it an open-source application?

SQuirreL SQL Client is a single, open-source graphical front end, Java-written program that enables you to issue SQL commands, perform SQL functions, and view the contents of a database. JDBC-compliant databases are supported by the built graphical front end. It also uses the most popular choice for the open-source software which is the GNU General Public License v2.0.

Will SQuirreL SQL run on both Linux and Windows?

SQuirreL is available under an open-source license and a popular Java written SQL database client. It runs under Microsoft Windows, Linux, and macOS.

Here are the supported databases of SQuirreL SQL:

  • Apache Derby
  • Hypersonic SQL
  • Axion Java RDBMS
  • H2 (DBMS)
  • ClickHouse
  • InterBase
  • Ingres (also OpenIngres)
  • Informix
  • InstantDB
  • IBM DB2 for Windows, Linux, and OS/400
  • Microsoft SQL Server
  • Microsoft Access with the JDBC/ODBC bridge
  • MySQL
  • Mimer SQL
  • Mckoi SQL Database
  • MonetDB
  • Netezza
  • Oracle Database 8i, 9i, 10g, 11g
  • PostgreSQL 7.1.3 and higher
  • Pointbase
  • Sybase
  • SAPDB
  • Sunopsis XML Driver (JDBC Edition)
  • Teradata Warehouse
  • Vertica Analytic Database
  • Firebird with JayBird JCA/JDBC Driver

What are the most essential SQuirreL SQL documentation links?

SQuirreL SQL Universal SQL Client

Install SQuirreL for Linux/Windows/others:

Install SQuirreL for MacOS x:

Install latest snapshots:

Overview of all available downloads:

Introducing DBeaver

Advertisements

With high data volumes and complex systems, database management is becoming more in-demand in today’s economy. Aside from keeping up with the business, organizations also need to innovate new ideas to progress further in the industry. With the use of database management tools, a web interface is provided for database administrators, allowing SQL queries to run.

What is DBeaver?

DBeaver is an open-source universal management tool that can help anyone in professionally working with their data. It will help you maneuver your data similar to a typical spreadsheet, construct analytical reports of various data storage records, and convey information. Effective SQL-editor, connection sessions monitoring, many administration features, and schema and data migration capabilities are imparted with DBeaver’s user on the advanced database. Aside from its usability, it also supports a wide array of databases.

Here are the other offers of DBeaver:

  • Cloud data sources support
  • Security standard of enterprise support
  • Support of multiplatform
  • Meticulous design and implementation of user interface
  • Can work with other integration extensions

Will it run on both Linux and Windows?

DBeaver is downloadable for Windows 9/8/10, Mac OS X, and Linux. It requires at least Java 1.8 version, and OpenJDK 11 bundle is already included in DBeaver’s MacOS and Windows installer.

Main features of DBeaver

DBeaver main features include:

  • Various data sources connection
  • Edit and view data
  • Advanced security
  • Generate mock-data
  • Built-in SQL editor
  • Builds the visual query
  • Transfer data
  • Compares several database structures
  • Search metadata
  • And generates schema/database ER diagrams.

Which databases or database types does DBeaver support?

More than 80 databases are supported by DBeaver, and it includes some of the well-known databases such as:

  • MySQL
  • Oracle
  • MS Access
  • SQLite
  • Apache Hive
  • DB2
  • PostgreSQL
  • Firebird
  • Presto
  • Phoenix
  • SQL Server
  • Teradata
  • Sybase

What are the most essential documentation links?

Related References

SQL Server Length Function Equivalent

Advertisements

The purpose of the Length function in SQL

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS).

The LENGTH Function Syntax

  • LENGTH(string)

LENGTH Function Notes

  • If the input string is empty, the LENGTH returns 0.
  • If the input string is NULL, the LENGTH returns NULL.

Length Function Across Databases

When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Working with VQL and SQL Server got me thing about the LENGTH() function, so, here is a quick references list, which does include the SQL Server.  

IBM DB2

  • LENGTH( )

IBM Informix

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

MariaDB

  • LENGTH( )

Microsoft SQL Server

  • LEN( )

MySQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

Netezza

  • LENGTH( )

Oracle

  • LENGTH( )

PostgreSQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

SOQL (SalesForce)

  • SOQL has no LENGTH function

VQL (Denodo)

  • LEN( )

Technology – Denodo SQL Type Mapping

Advertisements

denodo 7.0 saves some manual coding when building the ‘Base Views’ by performing some initial data type conversions from ANSI SQL type to denodo Virtual DataPort data types. So, where is a quick reference mapping to show to what the denodo Virtual DataPort Data Type mappings are:

ANSI SQL types To Virtual DataPort Data types Mapping

ANSI SQL TypeVirtual DataPort Type
BIT (n)blob
BIT VARYING (n)blob
BOOLboolean
BYTEAblob
CHAR (n)text
CHARACTER (n)text
CHARACTER VARYING (n)text
DATElocaldate
DECIMALdouble
DECIMAL (n)double
DECIMAL (n, m)double
DOUBLE PRECISIONdouble
FLOATfloat
FLOAT4float
FLOAT8double
INT2int
INT4int
INT8long
INTEGERint
NCHAR (n)text
NUMERICdouble
NUMERIC (n)double
NUMERIC (n, m)double
NVARCHAR (n)text
REALfloat
SMALLINTint
TEXTtext
TIMESTAMPtimestamp
TIMESTAMP WITH TIME ZONEtimestamptz
TIMESTAMPTZtimestamptz
TIMEtime
TIMETZtime
VARBITblob
VARCHARtext
VARCHAR ( MAX )text
VARCHAR (n)text

ANSI SQL Type Conversion Notes

  • The function CAST truncates the output when converting a value to a text, when these two conditions are met:
  1. You specify a SQL type with a length for the target data type. E.g. VARCHAR(20).
  2. And, this length is lower than the length of the input value.
  • When casting a boolean to an integertrue is mapped to 1 and false to 0.

Related References

denodo 8.0 / User Manuals / Virtual DataPort VQL Guide / Functions / Conversion Functions

Technology – Denodo Data Virtualization Project Roles

Advertisements

A Denodo virtualization project typically classifies the project duties of the primary implementation team into four Primary roles.

Denodo Data Virtualization Project Roles

  • Data Virtualization Architect
  • Denodo Platform Administrator
  • Data Virtualization Developer
  • Denodo Platform Java Programmer
  • Data Virtualization Internal Support Team

Role To Project Team Member Alignment

While the denodo project is grouped into security permissions and a set of duties, it is import to note that the assignment of the roles can be very dynamic as to their assignment among project team members.  Which team member who performs a given role can change the lifecycle of a denodo project.  One team member may hold more than one role at any given time or acquire or lose roles based on the needs of the project.

Denodo virtualization Project Roles Duties

Data Virtualization Architect

The knowledge, responsibilities, and duties of a denodo data virtualization architect, include:

  • A Deep understanding of denodo security features and data governance
  • Define and document5 best practices for users, roles, and security permissions.
  • Have a strong understanding of enterprise data/information assets
  • Defines data virtualization architecture and deployments
  • Guides the definition and documentation of the virtual data model, including, delivery modes, data sources, data combination, and transformations

Denodo Platform Administrator

The knowledge, responsibilities, and duties of a Denodo Platform Administrator, Include:

  • Denodo Platform Installation and maintenance, such as,
    • Installs denodo platform servers
    • Defines denodo platform update and upgrade policies
    • Creates, edits, and removes environments, clusters, and servs
    • Manages denodo licenses
    • Defines denodo platform backup policies
    • Defines procedures for artifact promotion between environments
  • Denodo platform configuration and management, such as,
    • Configures denodo platform server ports
    • Platform memory configuration and Java Virtual Machine (VM) options
    • Set the maximum number of concurrent requests
    • Set up database configuration
      • Specific cache server
      • Authentication configuration for users connecting to denodo platform (e.g., LDAP)
      • Secures (SSL) communications connections of denodo components
      • Provides connectivity credentials details for clients tools/applications (JDBC, ODBC,,,etc.)
      • Configuration of resources.
    • Setup Version Control System (VCS) configuration for denodo
    • Creates new Virtual Databases
    • Create Users, roles, and assigns privileges/roles.
    • Execute diagnostics and monitoring operations, analyzes logs and identifies potentials issues
    • Manages load balances variables

Data Virtualization Developer

The Data Virtualization Developer role is divided into the following sub-roles:

  • Data Engineer
  • Business Developer
  • Application Developer

the knowledge, responsibilities, and duties of a Denodo Data Virtualization Developer, by sub-role, Include:

Data Engineer

The denodo data engineer’s duties include:

  • Implements the virtual data model construction view by
    • Importing data sources and creating base views, and
    • Creating derived views applying combinations and transformations to the datasets
  • Writes documentation, defines testing to eliminate development errors before code promotion to other environments

Business Developer

The denodo business developer’s duties include:

  • Creates business vies for a specific business area from derived and/or interface views
  • Implements data services delivery
  • Writes documentation

Application Developer

The denodo application developer’s duties include:

  • Creates reporting vies from business views for reports and or datasets frequently consumed by users
  • Writes documentation

Denodo Platform Java Programmer

The Denodo Platform Java Programmer role is an optional, specialized, role, which:

  • Creates custom denodo components, such as data sources, stored procedures, and VDP/iTPilot functions.
  • Implements custom filters in data routines
  • Tests and debugs any custom components using Denodo4e

Data Virtualization Internal Support Team

The denodo data virtualization internal support team’s duties include

  • Access to and knowledge of the use and trouble of developed solutions
  • Tools and procedures to manage and support project users and developers

denodo Virtualization – Useful Links

Advertisements

Here are some denodo Virtualization references, which may be useful.

Reference Name Link
denodo Home Page https://www.denodo.com/en/about-us/our-company
denodo Platform 7.0 Documentation https://community.denodo.com/docs/html/browse/7.0/
denodo Knowledge Base and Best Practices https://community.denodo.com/kb/
denodo Tutorials https://community.denodo.com/tutorials/
denodo Express 7.0 Download https://community.denodo.com/express/download
Denodo Virtual Data Port (VDP) https://community.denodo.com/kb/download/pdf/VDP%20Naming%20Conventions?category=Operation
JDBC / ODBC drivers for Denodo https://community.denodo.com/drivers/
Denodo Governance Bridge – User Manual https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual
Virtual DataPort VQL Guidehttps://community.denodo.com/docs/html/browse/7.0/vdp/vql/introduction/introduction
Denodo Model Bridge – User Manualhttps://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Model%20Bridge%20-%20User%20Manual
Denodo Connects Manualshttps://community.denodo.com/docs/html/browse/7.0/denodoconnects/index
Denodo Infosphere Governance Bridge – User Manualhttps://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual

PostgreSQL – Useful Links

Advertisements

PostgreSQLUseful Links

Here are some PostgreSQL references, which may be useful.

Reference Type Link
PostgreSQL Home page & Download https://www.postgresql.org/
PostgreSQL Online Documentation https://www.postgresql.org/docs/manuals/
Citus – Scalability and Parallelism Extension https://github.com/citusdata
Free PostgreSQL Training https://www.enterprisedb.com/free-postgres-training
PostgreSQL Wiki https://wiki.postgresql.org/wiki/Main_Page

The Business Industries Using PostgreSQL

Advertisements

PostgreSQL is an open-source database, which was released in 1996. So, PostgreSQL has been around a long time.   So, among the many companies and industries which know they are using PostgreSQL, many others are using PostgreSQL and don’t know it because it is embedded as the foundation in some other application’s software architecture.  

I hadn’t paid much attaint to PostgreSQL even though it as been on the list leading databases used by business for years.  Mostly I have been focused on the databases my customer were using (Oracle, DB2, Microsoft SQL Server, and MySQL/MariaDB).  However, during a recent meeting I was surprised to learn that io had been using and administering PostrgresSQL embedded as part of another software vendors application, which made me take the time to pay attention to PostgreSQL. Especially, who is using PostgreSQL and what opportunities that may provide for evolving my career? 

The Industries Using PostgreSQL

According to enlyft, the major using the PostgreSQL are Computer Software and Information Technology And services companies.  

  PostgreSQL Consumers Information

Here is the  link to enlyft page, which provides additional information companies and industries using PostgreSQL:

Netezza / PureData – How To Get A List Of When A Store Procedure Was Last Changed Or Created

Advertisements

In the continuing journey to track down impacted objects and to determine when the code in a database was last changed or added, here is another quick SQL, which can be used in Aginity Workbench for Netezza to retrieve a list of when Store Procedures were last updated or were created.

SQL List of When A Stored Procedure was Last Changed or Created

select t.database — Database
, t.OWNER — Object Owner
, t.PROCEDURE — Procedure Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime

from _V_OBJECT o
, _v_procedure t
where
o.objid = t.objid
and t.DATABASE = ‘<<Database Name>>
order by o.objmodified Desc, o.objcreated Desc;

Related References

Netezza / PureData – How To Get a SQL List of When View Was Last Changed or Created

Advertisements

Netezza / PureData SQL (Structured Query Language)

Sometimes it is handy to be able to get a quick list of when a view was changed last.  It could be for any number of reason, but sometimes folks just lose track of when a view was last updated or even need to verify that it hadn’t been changed recently.  So here is a quick SQL, which can be dropped in Aginity Workbench for Netezza to create a list of when a view was created or was update dated last.  Update the Database name in the SQL and run it.

SQL List of When A view was Last Changed or Created

select t.database — Database
, t.OWNER — Object Owner
, t.VIEWNAME — View Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime

from _V_OBJECT o
,_V_VIEW_XDB t
where
o.objid = t.objid
and DATABASE = ‘<<Database Name>>
order by o.objcreated Desc, o.objmodified Desc;

Related References

 

Netezza / PureData – Table Describe SQL

Advertisements

Netezza / Puredata Table Describe SQL

If you want to describe a PureData / Netezza table in SQL, it can be done, but Netezza doesn’t have a describe command.  Here is a quick SQL, which will give the basic structure of a table or a view.  Honestly, if you have Aginity Generating the DDL is fast and more informative, at least to me.  If you have permissions to access NZSQL you can also use the slash commands (e.g. d).

Example Netezza Table Describe SQL

select  name as Table_name,

owner as Table_Owner,

Createdate as Table_Created_Date,

type as Table_Type,

Database as Database_Name,

schema as Database_Schema,

attnum as Field_Order,

attname as Field_Name,

format_type as Field_Type,

attnotnull as Field_Not_Null_Indicator,

attlen as Field_Length

from _v_relation_column

where

name='<<Table Name Here>>’

Order by attnum;

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Command-line options for nzsql, Internal slash options

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza getting started tips, About the Netezza data warehouse appliance, Commands and queries, Basic Netezza SQL information, Commonly used nzsql internal slash commands

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL introduction, The nzsql command options, Slash options

 

 

Netezza / PureData – Substring Function Example

Advertisements

The function Substring (SUBSTR) in Netezza PureData provides the capability to parse character type fields based on position within a character string.

Substring Functions Basic Syntax

SUBSTRING Function Syntax

SUBSTRING(<<CharacterField>>,<< StartingPosition integer>>, <<for Number of characters Integer–optional>>)

SUBSTR Function Syntax

SUBSTR((<>,<< StartingPosition integer>>, <>)

Example Substring SQL

Netezza / PureData Substring Example

Substring SQL Used In Example

SELECT  LOCATIONTEXT

— From the Left Of the String

— Using SUBSTRING Function

,’==SUBSTRING From the Left==’ as Divider1

,SUBSTRING(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,7,6) as Middle_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,15) as End_Using_SUBSTRING_LFT

,’==SUBSTR From the Left==’ as Divider2

—Using SUBSTR Function

,SUBSTR(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,7,6) as Middle_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,15) as End_Using_SUBSTR_LFT

— From the right of the String

,’==SUBSTRING From the Right==’ as Divider3

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTRING_RGT

,’==SUBSTR From the right==’ as Divider4

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTR_RGT

FROM BLOG.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’;

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Substring Function On Specific Delimiter

Advertisements

Netezza / PureData – Substring Function On Specific Delimiter

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.  However, it is possible, with a little creativity, to substring based on the position of a character in the string. This approach give more flexibility to the substring function and makes the substring more useful in many cases. This approach works fine with either the substring or substr functions.  In this example, I used the position example provide the numbers for the string command.

 

Example Substring SQL

Netezza PureData Substring Function On Specific Character In String

 

Substring SQL Used In Example

select LOCATIONTEXT

,position(‘,’ in LOCATIONTEXT) as Comma_Postion_In_String

—without Adjustment

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)) as Substring_On_Comma

—Adjusted to account for extra space

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Substring_On_Comma_Ajusted

,’==Breaking_Up_The_Sting==’ as Divider

— breaking up the string

,SUBSTRING(LOCATIONTEXT,1, position(‘ ‘ in LOCATIONTEXT)-1) as Beggining_of_String

,SUBSTRING(LOCATIONTEXT,position(‘ ‘ in LOCATIONTEXT)+1, position(‘ ‘ in LOCATIONTEXT)-1) as Middle_Of_String

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as End_Of_String

 

FROM Blog.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Position Function

Advertisements

Netezza / PureData Position Function

 

The position function in Netezza is a simple enough function, it just returns the number of a specified character within a string (char, varchar, nvarchar, etc.) or zero if the character not found. The real power of this command is when you imbed it with character function, which require a numeric response, but the character may be inconsistent from row to row in a field.

The Position Function’s Basic Syntax

position(<<character or Character String>> in <<CharacterFieldName>>)

 

Example Position Function SQL

Netezza PureData Position Function

 

Position Function SQL Used in Example

select LOCATIONTEXT, CITY

,’==Postion Funtion Return Values==’ as Divider

,position(‘,’ in LOCATIONTEXT) as Postion_In_Nbr_String

,position(‘-‘ in LOCATIONTEXT) as Postion_Value_Not_Found

,’==Postion Combined with Substring Function==’ as Divider2

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Position_Used_in_Substring_Function

FROM Blog.D_ZIPCODE  where STATE = ‘MN’ AND CITY = ‘RED WING’ limit 1;

 

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

 

PureData / Netezza – What date/time ranges are supported by Netezza?

Advertisements

Date/Time ranges supported by Netezza

Here is a synopsis of the temporal ranges ( date, time, and timestamp), which Netezza / PureData supports.

Temporal Type

Supported Ranges

Size In Bytes

Date

A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes

Time

An hour, minute, and second to six decimal places (microseconds). Values range from 00:00:00.000000 to 23:59:59.999999. 8 bytes

Related References

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza / PureData – How to add a Foreign Key

Advertisements
DDL (Data Definition Language)

Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).

Foreign Key (FK) Guidelines

  • A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key
  • Avoid using distribution keys as foreign keys
  • Foreign Key field should not be nullable
  • Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)
  • Apply standard naming conventions to constraint name:
    • FK_<<Constraint_Name>>_<<Number>>
    • <<Constraint_Name>>_FK<<Number>>
  • Please note that foreign key constraints are not enforced in Netezza

Steps to add a Foreign Key

The process for adding foreign keys involves just a few steps:

  • Verify guidelines above
  • Alter table add constraint SQL command
  • Run statistics, which is optional, but strongly recommended

Basic Foreign Key SQL Command Structure

Here is the basic syntax for adding Foreign key:

ALTER TABLE <<Owner>>.<<NAME_OF_TABLE_BEING_ALTERED>>

ADD CONSTRAINT <<Constraint_Name>>_fk<Number>>

FOREIGN KEY (<<Field_Name or Field_Name List>>) REFERENCES <<Owner>>.<<target_FK_Table_Name>.(<<Field_Name or Field_Name List>>) <<On Update | On Delete>> action;

Example Foreign Key SQL Command

This is a simple one field example of the foreign key (FK)

ALTER TABLE Blog.job_stage_fact

ADD CONSTRAINT job_stage_fact_host_dim_fk1

FOREIGN KEY (hostid) REFERENCES Blog.host_dim(hostid) ON DELETE cascade ON UPDATE no action;

Related References

Alter Table

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, Alter Table, constraints

Database – What is a foreign key?

Advertisements
Acronyms, Abbreviations, Terms, And Definitions

Definition of a Foreign Key

  • A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table.

Facts About Foreign Keys

  • Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the table keys
  • Foreign keys are not enforced by all RDBMS
  • The concept of referential integrity is derived from foreign key theory
  • Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
  • A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
  • The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys

Cascade

  • The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.

No Action

  • Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records.   No Action does not mean to suppress the foreign-key constraint.

Set null

  • The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.

Set default

  • The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record

Related References

Netezza / PureData – How to rebuild a Netezza view in Aginity

Advertisements

Rebuilding Netezza view sometimes becomes necessary when the view’s source table have changed underneath the view.  Rebuilding a view can be done on Netezza or in Aginity. In Aginity, it is a simple process, assume your user has permissions to create or replace a view.  The process breaks down into just a few steps:

Generate the create / replace view SQL of the original view into the query window, if you don’t have it already

In the object browser:

  • Navigate to the Database and view you wish to rebuild
  • Select the view and right click
  • Select ‘Scripts’, then ‘DDL to Query window’

Make may updates to create / replace View SQL

  • This step is not always necessary, sometimes the changes which invalided the view did not actually impact the code of the view. If changes are necessary, make may updates to the SQL code.

Execute The code

  • This I usually do by choosing the ‘Execute as a single batch’ option.  Make sure the code executes successfully.

Verify the view

  • To verify the simply execute a select statement and make it executes without errors and/or warning.

Netezza / PureData Date – Difference in Days SQL

Advertisements

Netezza /PureData Date Difference in Days

Since Netezza does not have a datediff function, the ‘old school’ of calculating the difference, in days, between dates must be used.

Subtracting Inclusive Dates

To subtract to day and include end date, as a day, in calculation (1 day is added)

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates

From _v_dual;

 

Subtracting Non-inclusive dates

To subtract dates non-inclusive simply subtract the dates

select date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Example SQL From Graphic

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates,

date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Related References

What are the types of Database Management Systems (DBMS)?

Advertisements

Database Management Systems (DBMS)

It is important to understand the differences between Database Management Systems (DBMS) types, since the structure of each type will influence integrations approaches, functionality, overall speed, and scalability.

The Five Types of Database Management Systems (DBMS)?

The five basic types of databases are:

  • Hierarchical
  • Object-Orientated
  • Network
  • Relational
  • Flat File

Hierarchical Database (HDB)

  • A hierarchical database (HDB or HDBMS)is a design that uses a one-to-many relationship for data elements. Hierarchical database models use a tree structure that links several disparate elements to one “owner,” or “parent,” primary record.

Object-Oriented Database (OODB)

  • Object-Orientated databases (OODB or OODBM) integrate object orientation with database capabilities. Object orientation allows a more direct representation and modeling of real-world problems, and database functionality is needed to ensure persistence and concurrent sharing of information in applications.

Network Database (NDB)

  • Network databases (NDB or NDBMS) are quite like hierarchical databases, except it allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree. The multiple linkages which this information allows the network database model to be very flexible. In addition, the relationship that the information has in the network database model is defined as many-to-many relationship because one owner file can be linked to many member files and vice versa.

Relational Database (RDB)

  • In simplest terms, a relational database (RDB or RDBMS)is one that presents information in formally described tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related per common keys or concepts, and the ability to retrieve related data from a table, which is the basis for the term relational database. Data can be accessed or reassembled in many ways without having to reorganize the database tables structure.

Flat File Database (FFDB)

  •  A flat file database (FFDB or FFDBM) describes any of various means to encode a database model as a single file or collection of files, which can be a plain text file or a binary file. There are usually no structural relationships between the records. Each line of the text file holds one record, with fields separated by delimiters, such as commas or tabs. This is a very old database approach, but can still be found in use to, often with some relation capability enhancements,.  Some example of current flat file databases are: GRAV, Jekyll, Kerby, and Monstra.

Related References

 

Database – What is a Composite Primary Key?

Advertisements
Database Table

What is a Composite Primary Key?

A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it.  Like a Primary Key what a composite Primary Key is depends on the database.  Essentially a Composite Primary Key:

  • Is a combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And may define parent-Child relationship between tables

Related References

Database – What is a Primary Key?

Advertisements
Database Table

What is a primary Key?

What a primary key is depends, somewhat, on the database.  However, in its simplest form a primary key:

  • Is a field (Column) or combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And, depending on the data model can, define parent-Child relationship between tables

Related References

Netezza / PureData – Substring Function

Advertisements

Substring is a common enough function in SQL, however, the exact language format used to perform this function can vary from one database to another.  So, here are a few quick notes on the substring format in Netezza / PureData.

What is the purpose of a substring?

SUBSTRING allows SQL to extract part of a string with a field, which in Netezza / PureData is based on positions with the string.

Substring Function Syntax

Substring(<<Input Field>>)from <<start-position>> [for <<length in Characters>>]).

Notes:
·        Square brackets ‘[ ]’ indicate optional content, which can be useful under certain circumstances
·        If you do not specify the optional for length [for <<length in Characters>>], then the remained of the string will be returned

Example Substring SQL Using ‘For Length’ property

Example Substring SQL

SELECT  SUBSTRING(DD.DATE_SRKY FROM 1 FOR 4)  as  First_Four_Digits

, SUBSTRING(DD.DATE_SRKY FROM 5 FOR 2)  as  Middle_Two_Digits

, SUBSTRING(DD.DATE_SRKY FROM 7 FOR 2)  as  Last_Two_Digits

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

Example Substring SQL without the optional ‘For Length’ property

Example Substring SQL

SELECT  SUBSTRING(DD.DATE_SRKY FROM 5 )  as  Remainder_String_From_Position_5

, SUBSTRING(DD.DATE_SRKY FROM 7 )  as  Remainder_String_From_Position_7

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

Example Substring SQL which bring the parts together in a new format

Example Substring SQL

SELECT  SUBSTRING(DD.DATE_SRKY FROM 1 FOR 4)||’-‘||

SUBSTRING(DD.DATE_SRKY FROM 5 FOR 2)||’-‘||

SUBSTRING(DD.DATE_SRKY FROM 7 FOR 2) ||  ‘ 00:00:00’ as Reassembled_As_Date_Format

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Row number within a group

Advertisements

Sometimes it is useful to be able to number data with its groups, which can be quickly and easily done using the Row Number function in Netezza. However, you do need to pay particular attention to you ‘partition by’ (conceptually a group by operation) field list and field order, as well as, you ‘Order by’ field list.  Otherwise, you can get some bizarre results.  it finds it helpful to think of them as having a parent, child, relationship.  In this line of thought, your ‘partition by’ field list should usually have one less more field than your ‘order by’.  This is not an absolute rule but under normal circumstances your  ‘order by’ field list should contain all the field in your ‘partition by’  and, occasionally, provide an order by direction (ascending or descending).

SQL Netezza Row Number Format

ROW_NUMBER() OVER( PARTITION BY <<Partition_By_Field_list>> ORDER BY <<Sort_By_Field_List [direction asc/desc]>> ) as ROW_NUM

Example Row Number SQL

SELECT

qh_database

,qh_sessionid

, qh_tend

,ROW_NUMBER() OVER( PARTITION BY qh_database, qh_sessionid

ORDER BY qh_database, qh_sessionid, qh_tend desc ) as ROW_NUM

FROM nz_query_history

where qh_database = ‘system’

order by qh_database, qh_sessionid, qh_tend desc

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

Netezza / PureData – Case Statement Example

Advertisements

The Case Statement is one which I occasionally use within Netezza/PureData SQL and Stored Procedures.  Basically, the Case Function provides an ‘IF-THEN-ELSE’ decision capability.  This ‘IF-THEN-ELSE’ capability allows the evaluation conditions and return a value when the first condition is met and/ or else break out logic if the condition or conditions are not met.

Case Function Language Structure

·       Case Function Basic Structure

CASE

WHEN <search-condition-1> THEN <result-1>

WHEN <search-condition-2> THEN <result-2>

WHEN <search-condition-n> THEN <result-n>

ELSE <default-result>

END

·       Case Function Simple Structure

CASE

WHEN <search-condition-1> THEN <result-1>

ELSE <default-result>

END

Case Function Embedded in SQL Select Example

SQL Used in Embedded Example

Select

now() as “Time”,

current_date as “Today”,

(

case

when (DATE_PART(‘HOUR’,NOW())>12 )

THEN date(current_date +  cast(‘1 days’ as interval))

ELSE  current_date

END ) as “PROCESSING_DATE”

From ADMIN._v_dual;

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Functions, and operators, Functions