NETEZZA SQL – DAY 2

NETEZZA
APPLIANCE
Danilo De Benedictis – [email protected]
NETEZZA SQL – DAY 2
ACCESSO A NETEZZA SQL CON NZSQL

Netezza SQL
SQL 92: standard ANSI/ISO (full compliant)
 SQL/CLI: supporto al Client/Server (ODBC, JDBC)
 SQL:1999: supporto alle RegExpr, Recurs.Query,
triggers, O.O.

nzsql: Usato per lanciare comandi ‘Netezza
SQL’ verso il database
 nzsql: lanciato da shell LINUX.
 Multiple Connection / Single SQL Activity per
Connection.
 nzsql :TERMINARE CON ‘;’ IL COMANDO....
 \q per uscire dal prompt

ACCESSO A NETEZZA SQL CON NZSQL

Log On:
 nzsql
-d DB_VENDITE -u danilode -pw
Passw0rd!
 oppure
 export
NZ_USER=danilode
 export NZ_PASSWORD= Passw0rd!
 export NZ_DATABASE= DB_VENDITE
 Nzsql
 Per
cambiare connessione:
 \c[onnect]
[dbname [user] [password]]
ACCESSO A NETEZZA SQL CON NZSQL

Nzsql prompt result:
nzsql
CREATE TABLE test1 (col1 INTEGER, col2 INTEGER, col3 CHARACTER(40));
CREATE TABLE
INSERT INTO test1 VALUES (100, 200, 'This is a test');
INSERT 0 1 (lo 0 è retaggio storico..)
INSERT INTO test1 VALUES (101, 201, 'Another test');
INSERT 0 1
UPDATE test1 SET col2 = 999 WHERE col1 < 1000;
UPDATE 2
INSERT INTO test1 SELECT * FROM test1;
INSERT 0 2
delete from test1 where col1 > 0;
DELETE 4
TRUNCATE TABLE test1;
TRUNCATE TABLE
DROP TABLE test1;
DROP TABLE
ACCESSO A NETEZZA SQL CON NZSQL

Management delle Sessioni:
 SHOW
SESSION [ ALL | <session-id> ] [
VERBOSE ]
 ALTER SESSION [ <session-id> ] ROLLBACK
TRANSACTION
 ALTER SESSION [<session_id>] SET PRIORITY
TO <priority>
 DROP SESSION <session-id>
ACCESSO A NETEZZA SQL CON NZSQL

Supporto SSL per i Client:
 export
NZ_SECURITY_LEVEL=level
 export NZ_CA_CERT_FILE=pathname
ACCESSO A NETEZZA SQL CON NZSQL
Variabili SQL di Sessione:
DB_VENDITE(danilode)=> \set
VERSION = 'Netezza SQL Version 1.1'
PROMPT1 = '%/%(%n%)%R%# '
PROMPT2 = '%/%(%n%)%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
DBNAME = DB_VENDITE '
USER = danilode '
HOST = '127.0.0.1'
PORT = '5480'
ENCODING = 'LATIN9'
NZ_ENCODING = 'UTF8'

ACCESSO A NETEZZA SQL CON NZSQL

COMMAND INPUT:
 SQL
COMMAND DA STRINGA:
 nzsql
 SQL
COMMAND DA STANDARD INPUT:
 nzsql
 SQL
-c "select * from mia_tabella“
< foo.sql
COMMAND DA FILE
 nzsql
-f foo.sql
ACCESSO A NETEZZA SQL CON NZSQL

COMMAND OUTPUT:
 OUTPUT
IN VARIABILE:
 VAR1=‘nzsql
-A -t -c "select count(*) from
test_table"‘
 OUTPUT
 nzsql
| lpr
 OUTPUT
 nzsql

AD UNA STAMPANTE:
SU UN FILE:
-o report.out
Successivamente, tutti i comandi lanciati da nzsql vanno
sul file report.out
ACCESSO A NETEZZA SQL CON NZSQL
nzsql COMMAND
OPTIONS:
ACCESSO A NETEZZA SQL CON NZSQL
nzsql COMMAND
OPTIONS:
ACCESSO A NETEZZA SQL CON NZSQL

COMMAND OPTIONS (comments/labels):
(doppio ‘meno) : riga di commento
 /* */: inizio – fine commento (multiriga)
 ‘stringa’: gli apici SINGOLI racchiudono le
stringhe
 “NOME COLONNA”: i DOPPI apici
racchiudono le labels.
 --
 Ultime
versioni di Netezza SQL usano upper case:
per usare lower case nelle labels, racchiudere tra
doppi apici (i.e. CREATE TABLE “Employee”)
ACCESSO A NETEZZA SQL CON NZSQL
Backslash
 Options:
 Dall’interno
di nzsql

ACCESSO A NETEZZA SQL CON NZSQL
Backslash
 Options:
 Dall’interno
di nzsql

ACCESSO A NETEZZA SQL CON NZSQL
Backslash
 Options:
 Dall’interno
di nzsql

ACCESSO A NETEZZA SQL CON NZSQL

Query Buffer, per editare comandi SQL più
complessi:
\e lancia un editor esterno per editing della query.
Alla chiusura dell’editor, la query viene lanciata.
 Editor vi di default:

 Per
cambiare editor: export EDITOR=emacs
\p contenuto del buffer
 \r reset del buffer (cancellazione)
 \w <filename> scrive il contenuto del buffer su file

ACCESSO A NETEZZA SQL CON NZSQL

Exit Code:
0
: Success
 1 : Errore generico (Sintassi, problema sul DB,
etc..)
 2 : Errore di connessione (nome DB sbagliato,
username/password sbagliata, etc..)
 3 : Query cancellata dall’utente (Control+C)

Eseguendo i comandi via file (-f):
 Nzsql
continua l’esecuzione anche su errore.
 Per fermare l’esecuzione in caso d’errore:
 ‘-v
ON_ERROR_STOP=1’
SCRIPTING

QUERY MULTIPLE IN UNICO FILE:
 nzsql
< script_file (STD IN da file)
 nzsql -f script_file (specifica il file name)
 EMP(USER)=> \i script_file (specifica il file name
stando già nell’ambiente interprete di comandi
nzsql).
NETEZZA DATATYPES
NETEZZA DATATYPES
USARE CON CAUTELA: approssimazione nelle
aggregazioni dipendono da ordinamenti interni,
e da parallelismo (unpredictable).
 Es. SUM e AVG sono parzialmente computate
dalle SPU (in parallelo), ed aggregate dall’host:
il risutato finale può essere diverso da run a run.

NETEZZA DATATYPES


COMPARE CON NUMBER: usare sempre to_number:
where to_number(<varchar-column>, '9999') >
<integercolumn>
NETEZZA DATATYPES

Valorizzare con:
true / false
 on / off
 ‘0’ / ‘1’
 ‘true’ / ‘false’
 ‘t’ / ‘f’
 ‘on’ / ‘off’
 ‘yes’ / ‘no’


Non usare mai un
BOOL come
DISTRIBUTION:
I
dati verrebbero
distribuiti solo su 2
slices dei dischi.
NETEZZA DATATYPES
NETEZZA DATATYPES

TIME INTERVAL : NON STANDARD
IMPLEMENTATION

Specifica unità di misura: sintassi accettata ma non
usata:
 Può
contenere intervalli di qualsiasi unità di misura (day,
year, etc.)
Literal: può contenere intervalli del tipo «13 years 4
months» per indicare un intervallo di 13 anni e 4 mesi.
Lo standard SQL non consente literals.
 Netezza normalizza tutti gli intervalli all’unità di misura
dei secondi.
 Netezza considera un mese sempre di 30 gg, per
evitare errori usare sempre intervalli con unit < mese
(es. day).
 I TIMEINTERVAL non possono essere applicati alle
external table.

NETEZZA DATATYPES

INTERNAL DATATYPES:
Rowid: identificativo univoco di un record
all’interno del database (max = 2^64 / 2)
 Netezza assegna un intervallo x ogni SPU
 Ad intervallo consumato, Netezza assegna un
altro intervallo libero, ma non necessariamente
contiguo


=> gli intervalli non sono necessariamente sequenziali
nella stessa tabella.
NETEZZA DATATYPES

INTERNAL DATATYPES:
 Createxid
: tranID che ha creato il record.
 Deletexid: tranID che ha cancellato il record.

UPDATE: cancellazione logica = delete+insert:
 tranID->deletexid
(riga cancellata)
 tranID->insertxid (nuova riga)
 Se possibile, non usare UPDATE (double space)
NETEZZA DATATYPES

INTERNAL DATATYPES:
datasliceID: identificativo univoco della porzione
di disco su cui è memorizzata la riga.
 Record <-> datasliceID <-> disco fisico

SELECT DATASLICEID, name FROM mia_tabella;
 Restituisce il data slice da cui proviene il record.
 [Esempio]

SINTASSI NETEZZA SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
GESTIONE DATABASE
ACCESSO AD ALTRI DATABASE
GESTIONE TABELLE
JOIN TRA TABELLE
COMBINE TRA TABELLE
GESTIONE VISTE
GESTIONE VISTE MATERIALIZZATE
SUBQUERY
FUNZIONI DI AGGREGAZIONE
SCRIPTING
GESTIONE DATABASE
CREATE DATABASE db_name;
 DROP DATABASE db_name;
 ALTER DATABASE db_name RENAME TO
newdb;
 Database MAXIMUM:

GESTIONE DATABASE : CALCOLO ROWSIZE


Disk size di ciascun campo;
3 x 8 byte overhead fisso per ogni riga:





Rowid
Createxid
Deletexid
N: #colonne nullable: (N/8) bytes in header, rounded a 4
bytes (un bit per ogni campo nullable).
4 bytes in header se esiste almeno una colonna di tipo:




VARCHAR
CHAR (len > 16)
NCHAR
NVARCHAR
GESTIONE DATABASE : SQL IDENTIFIERS
Sono i nomi di database, tabelle, campi,
user, gruppi, oggetti definiti da utente.
 128 byte lunghezza max
 WARNING: password e filename non sono
Identifiers.

GESTIONE DATABASE : SQL IDENTIFIERS

REGULAR IDENTIFIERS: nome di DB object
Non case-sensitive (sempre convertiti UPPER)
 Lettere+numeri+ ‘_’ + ‘$’

 1°
carattere lettera
 non usare parole riservate

DELIMITED IDENTIFIER: nome di DB object
Delimitato da doppi apici (es. " DB_Clienti")
 Case-sensitive
 Può includere anche:

 1°
carattere qualsiasi
 caratteri speciali (es. %, -, spazi)
 Parole riservate
ACCESSO AD ALTRI DATABASE

Nelle query, è possibile referenziare oggetti
presenti su diversi database, ma:
 Solo
oggetti presenti sullo stesso server fisico
 Non è possibile nella SELECT per la creazione
di MATERIALIZED VIEW

Nome referenza a 3-livelli:
 Db_name.schema_name.object_name
 Db_name..object_name
(usa default schema)
 schema_name.object_name (stesso DB)

Scripting: schema = nome del DB Owner
ACCESSO AD ALTRI DATABASE

Esempio:
SELECT COUNT (*)
 FROM

 DEV..EMP
DE ,
 PROD..EMP PE


WHERE DE.ID = PE.ID;
Esempio:
CREATE TABLE KEYEMPS AS
 SELECT *
 FROM

 PROD..EMP

INTERSECT
 SELECT
* FROM DEV..EMP;
ACCESSO AD ALTRI DATABASE

Non è possibile usare cross-database nelle
INSERT, UPDATE, or DELETE.
 dev(admin)=>INSERT
INTO PROD..EMP
SELECT * FROM EMP;
 Error: Cross Database Access not supported for
this type of command.

Usare invece:
 prod(admin)=>INSERT
FROM DEV..EMP;
INTO EMP SELECT *
ACCESSO AD ALTRI DATABASE

ALIAS
È
possibile usare ALIASes per nomi tabella e
nomi colonna
 Non sono persistenti (query scope)
 Es.
 dev(admin)=>FROM
emp E WHERE E.id =10
 dev(admin)=>FROM admin.emp E WHERE E.id =10
ACCESSO AD ALTRI DATABASE

SYNONYM




È possibile creare Sinonimi per referenziare oggetti sul
DB corrente o su altri DB:
dev(admin)=>CREATE SYNONYM p_emp FOR
prod..emp;
Consentite CREATE, DROP, ALTER(RENAME), GRANT,
REVOKE
ES.:




GRANT [CREATE] SYNONYM TO user_or_group;
GRANT ALTER, DROP ON synonym_name TO
user_or_group;
REVOKE [CREATE] SYNONYM FROM user_or_group;
REVOKE ALTER, DROP ON synonym_name FROM
user_or_group;
GESTIONE TABELLE

CREATE TABLE:
CREATE TABLE weather (
 city
varchar(80),
 temp_lo
int, -- low temperature
 temp_hi
int, -- high temperature
 prcp
real, -- precipitation
 date
date
 );


Parole riservate:ctid, oid, xmin, cmin, xmax,
cmax, tableoid, rowid, datasliceid, createxid,
and deletexid.
GESTIONE TABELLE
GESTIONE TABELLE
GESTIONE TABELLE
GESTIONE TABELLE

«...Note:






The system permits and maintains primary key, default,
foreign key, unique, and references.
The Netezza does not support constraint checks and
referential integrity. The user
must ensure constraint checks and referential integrity.»
Optimizer usa le PK e le FK per il query plan.
Esempio SQL (DBVisualizer)...
http://stackoverflow.com/questions/5649297/how-toovercome-netezzas-lack-of-unique-constraintreferential-integrity-enforc
GESTIONE TABELLE




DISTRIBUTION KEY: ogni tabella può avere 1 sola
Distribution K, composta da 1+ colonne (max 4).
[hash]: specificare i campi
[random]: applica una distribuzione round-robin,
generalmente buona, ma non sempre ottimale.
Void: se non si specificano campi, Netezza sceglie i
campi, nessuna garanzia su quali (variano tra le
versioni).
GESTIONE TABELLE
NETEZZA distribuisce i dati di una tabella in
funzione della DISTRIBUTION key (1..4 campi).
 I dati della tabella vengono ‘distribuiti’ sulle SPU
con un algoritmo di distribuzione:

 HASH:
F(fie1+..+fie4) -> Hash Alg -> #SPU.
 «NOME»
+ «COGNOME»
 «PIPPO» + «PLUTO»
 «Mario» + «Rossi»
 «013214» + «aa65h281» ->
 RANDOM:
->
->
->
HASH
ALGORITH
M
SPU #1
SPU #2
SPU #3
SPU #4
distribuzione casuale (non ottimale)
GESTIONE TABELLE

ERRATA
DISTRIBUTION:


Non scegliere campi con
un dominio di valori
limitato.
Esempio Boolean:

HASH: F(fie1 :
BOOLEAN):
 TRUE
SPU #1
->
 TRUE
->
 FALSE
 TRUE
->
SPU #2
->
HASH
ALGORITH
M
SPU #3
SPU #3
GESTIONE TABELLE

TIPS:
I
campi in Distribution key non sono UPDATEable.
 Scegliere Distribution Key che garantiscono una
distribuzione paritaria su tutte le SPU.
 Scegliere colonne con vincolo UNIQUE e con alta
cardinalità.
 Non utilizzare RANDOM per tabelle critical, ma
guidare la scelta.
 Specificare sempre la clausola DISTRIBUTE,
anche se si vuole usare il primo campo.
 Non distribuire su FLOAT o su BOOLEAN.
GESTIONE TABELLE

TIPS:
 Usare
il minor numero di campi possibile.
 COLLOCATED JOIN:
 Le
SPU possono funzionare in modalità «shared
nothing».
 se 2 tabelle vanno spesso in join sullo stesso campo,
usarlo come DISTRIB K. Es:

 in
In un sistema di gestione Ordini, usare come Distrib K il
Customer_id sia sulla tabella CUSTOMER che sulla ORDER.
questo modo, le SPU raggiungono il massimo livello
di parallelismo, in quanto non devono condividere /
trasmettersi nulla.
GESTIONE TABELLE

TIPS:
Se non è possibile usare le COLLOCATED JOIN, i dati
vengono dinamicamente redistribuiti, o inviati in
broadcast.
 DYNAMIC REDISTRIBUTION

 Ciascuna





SPU
Estrae solo le righe richieste
estrae solo le colonne richieste
Calcola la SPU di competenza per eseguire la join
Invia i dati direttamente alla SPU destinataria
DYNAMIC BROADCAST
 Ciascuna



SPU
Estrae tutte le righe della tabella
estrae tutte le colonne della tabella
Invia i dati in broadcast a tutte le SPU
GESTIONE TABELLE

CHECK TABLE DISTRIBUTION:
 SELECT
datasliceid, COUNT(*)
FROM <table_name>
GROUP BY datasliceid;
 4 Rows Returned (normally one per SPU)

DATASLICEID COUNT
 In
1
7
2
7
3
7
4
7
questo caso: distribuzione ottimale.
 WARNING: le colonne di distribuzione non
possono essere updated.
GESTIONE TABELLE

CHECK
RECORD
DISTRIBUTI
ON

evitare il
DATA
SKEW
(check con
NZADMIN
tool).
GESTIONE TABELLE

DATA SKEW (check con NZADMIN tool)
 Differenza
 max
in Mb tra l’allocazione di
data-slice di una tabella (Mb contenuti nel dataslice che contiene maggior porzione dati della tabella)
 min data-slice di una tabella (Mb contenuti nel data-slice
che contiene minor porzione dati della tabella)