Post on 27-Jul-2018
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 -c "select * from mia_tabella“
SQL COMMAND DA STANDARD INPUT:
nzsql < foo.sql
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 AD UNA STAMPANTE:
nzsql | lpr
OUTPUT SU UN FILE:
nzsql -o report.out
Successivamente, tutti i comandi lanciati da nzsql vanno
sul file report.out
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 apiciracchiudono le labels.Ultime versioni di Netezza SQL usano upper case:
per usare lower case nelle labels, racchiudere tradoppi apici (i.e. CREATE TABLE “Employee”)
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
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 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. GESTIONE DATABASE
2. ACCESSO AD ALTRI DATABASE
3. GESTIONE TABELLE
4. JOIN TRA TABELLE
5. COMBINE TRA TABELLE
6. GESTIONE VISTE
7. GESTIONE VISTE MATERIALIZZATE
8. SUBQUERY
9. FUNZIONI DI AGGREGAZIONE
10. 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 (*)
FROMDEV..EMP DE ,
PROD..EMP PE
WHERE DE.ID = PE.ID;
Esempio: CREATE TABLE KEYEMPS AS
SELECT *
FROMPROD..EMP
INTERSECTSELECT * 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 INTO EMP SELECT *
FROM DEV..EMP;
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
«...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-to-overcome-netezzas-lack-of-unique-constraint-referential-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: distribuzione casuale (non ottimale)
HASH
ALGORITH
M
SPU #1
SPU #2
SPU #3
SPU #4
GESTIONE TABELLEGESTIONE TABELLE
ERRATA DISTRIBUTION:
Non scegliere campi con un dominio di valori limitato.
Esempio Boolean:
HASH: F(fie1 : BOOLEAN): TRUE ->
TRUE->
FALSE ->
TRUE->
HASH
ALGORITH
M
SPU #1
SPU #2
SPU #3
SPU #3
GESTIONE TABELLEGESTIONE 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 TABELLEGESTIONE 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 un sistema di gestione Ordini, usare come Distrib K il
Customer_id sia sulla tabella CUSTOMER che sulla ORDER.
in questo modo, le SPU raggiungono il massimo livello
di parallelismo, in quanto non devono condividere /
trasmettersi nulla.
GESTIONE TABELLEGESTIONE TABELLE
TIPS:
Se non è possibile usare le COLLOCATED JOIN, i dati vengono dinamicamente redistribuiti, o inviati in broadcast.
DYNAMIC REDISTRIBUTIONCiascuna 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 BROADCASTCiascuna SPU
Estrae tutte le righe della tabella
estrae tutte le colonne della tabella
Invia i dati in broadcast a tutte le SPU
GESTIONE TABELLEGESTIONE TABELLE
CHECK TABLE DISTRIBUTION:
SELECT datasliceid, COUNT(*)
FROM <table_name>
GROUP BY datasliceid;
4 Rows Returned (normally one per SPU)
In questo caso: distribuzione ottimale.
WARNING: le colonne di distribuzione non
possono essere updated.
DATASLICEID COUNT
1 7
2 7
3 7
4 7
GESTIONE TABELLEGESTIONE TABELLE
CHECK RECORD DISTRIBUTION evitare il
DATA SKEW (check con NZADMIN tool).
GESTIONE TABELLEGESTIONE TABELLE
DATA SKEW (check con NZADMIN tool)
Differenza in Mb tra l’allocazione di
max data-slice di una tabella (Mb contenuti nel data-
slice 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)