NETEZZA APPLIANCE - di.univaq.it · ACCESSO A NETEZZA SQL CON NZSQL Log On: nzsql -d DB_VENDITE -u...

NETEZZA

APPLIANCEDanilo 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


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]]


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


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>


Supporto SSL per i Client:

export NZ_SECURITY_LEVEL=level

export NZ_CA_CERT_FILE=pathname


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'


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


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


nzsql COMMAND

OPTIONS:


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”)


Backslash

Options:

Dall’interno

di 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


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. 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


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;


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;


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


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

«...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

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


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.


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.


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


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


CHECK RECORD DISTRIBUTION evitare il

DATA SKEW (check con NZADMIN tool).


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)

NETEZZA APPLIANCE - di.univaq.it · ACCESSO A NETEZZA SQL CON NZSQL Log On: nzsql -d DB_VENDITE -u...

Documents

Transcript of NETEZZA APPLIANCE - di.univaq.it · ACCESSO A NETEZZA SQL CON NZSQL Log On: nzsql -d DB_VENDITE -u...