Il presente articolo è stato tradotto automaticamente.

SQL Server

Usa le tabelle aggiornabili per la creazione di report reattivi in tempo reale

Don Mackenzie

Sistemi di business includono spesso impegnativi obblighi di segnalazione. Un utente aziendale deve accedere storiche transazioni e attività corrente. Ha anche bisogno di visualizzare dati in molti modi. Un utente farà richieste specifiche come:

• Dollari di mese dal cliente per l'ultimo anno
• Unità di settimana di prodotto per gli ultimi sei mesi
• Unità e dollari di ordine e prodotto negli ultimi 10 giorni (compreso oggi e adesso)

Soddisfare queste richieste variate può essere una sfida per i progettisti di sistema, soprattutto per le imprese con un volume di transazioni elevate. Soluzioni digitali Cox (CD) è un buon esempio. L'azienda elabora circa 20.000 operazioni al secondo.

Il mio sistema di progettazione per i CD supporta fino a ore segnalazione con la storia di un anno in una tabella di database SQL Server con aggiornamenti ogni 10 minuti. La soluzione combina due funzionalità di SQL Server — partizionamento e indicizzazione columnstore — per realizzare questa risposta tempo quando una query una massiccia quantità di dati.

Prime impressioni

CD fornisce servizi pubblicitari Internet. Supportati da pubblicità televisive "Libero" e "liberi" di siti Web sono supportati da annunci visualizzati su quelle pagine. CD aiuta gli editori Web sito visualizzare correttamente gli annunci. L'azienda registra informazioni su ogni display di ogni annuncio (chiamato un'impressione). Registra anche altre informazioni quali i clic sugli annunci. Questo genera il record di quasi 2 miliardi (1,5 TB compressa) al giorno.

Ogni impressione, o la transazione, coinvolge numerosi componenti. Ci sono due clienti — l'inserzionista e l'editore del sito Web. Esso coinvolge anche il prodotto, che è l'annuncio. Inserzionisti effettuare ordini, quindi c'è anche un id ordine come parte di un record. C'è un prezzo raccolti dall'inserzionista e un prezzo pagato per l'editore. Il record di transazione contiene molti campi che riflettono numerosi piccoli dettagli sulla transazione.

Le transazioni di più di un anno vengono archiviate così un utente può paragonare il presente mese di un anno fa, o ringraziamento (un giorno di grande pubblicità) quest'anno allo scorso anno. CD genera report fattura inserzionisti e pagare gli editori. Queste relazioni aiutano anche il personale monitorare pubblicità-ordine consegna, aiutano a monitorare l'efficacia dei propri annunci inserzionisti e gli editori in grado di monitorare attività e ricavi.

Utenti business spesso non vedo l'ora per i dati di ogni nuovo giorno. Faranno fare aggiustamenti e aggiornamenti come gli annunci vengono consegnati in tutto il giorno. Questo mi ha portato alla progettazione descriverò qui, che può adattare per molte applicazioni dove un flusso continuo di nuovi dati è aggiunto un dataset già grande.

Sovraccarico dati

Non volete mettere 500 miliardi righe (e 500 TB decompresso) in una tabella del SQL Server . Le prime transazioni sono conservate in un Hadoop Distributed File System (HDFS). Hadoop è un buon strumento per memorizzare e analizzare enormi quantità di dati, ma ha tempo di risposta terribile per le query (ulteriori informazioni al hadoop.apache.org).

Le query di dati prendono da cinque minuti a 20 ore, a seconda, naturalmente, la quantità di dati, la finestra di tempo e la complessità della query. Fare una query direttamente su HDFS è troppo lento per gli utenti di applicazioni Web.

Esigenze della maggior parte degli utenti possono essere soddisfatte con dati aggregati. Le transazioni sono riassunti da ora (e da inserzionista, sito Web, annunci e altri attributi chiave) e inserite in una tabella di database SQL Server . Le ore sono importanti perché l'attività di pubblicità è molto diversa a mezzogiorno rispetto a 2 Questo processo viene riepilogato 70 milioni di transazioni di 50.000 righe all'ora.

Queste righe vengono inserite in una tabella SQL Server chiamata entrate. Un'applicazione Web interattiva permette all'utente di eseguire query su questa tabella per verificare le prestazioni di un ordine, vedere come un annuncio si svolge su diversi siti, revisione bilanci e così via. Questa tabella viene aggiornata e i dati di ora (50.000 righe) sono sostituiti ogni 10 minuti.

Partizionamento di una tabella del SQL Server

SQL Server 2005 ha introdotto un buon supporto per le tabelle partizionate, livello che ha continuato con le versioni successive. Partizionamento rompe un grande tavolo in alcuni (o molti) tabelle interne più piccole. SQL Server archivia e indicizza ogni tavolino, o partizione, separatamente (vedere Figura A).

Figura una tabella partizionata in SQL Server

Una funzione di partizione (essenzialmente un elenco di valori limite che separano le partizioni) racconta SQL Server come separare la tabella in partizioni. C'è spesso una colonna datetime (come nel mio tavolo entrate) è utile per la separazione di partizioni. Nella tabella è mensile partizioni per i dati di più di un mese vecchio quotidiano partizioni per i dati più recenti più di una settimana di vita e oraria divisori dati molto recenti. Tutte queste piccole partizioni hanno un nome di tabella unica, un unico schema e sembrano essere un grande tavolo per l'applicazione.

Quando SQL Server elabora una query con una clausola WHERE, compresa la colonna data, determina quali partizioni sono necessarie e ignora il resto. Tutte le query includono la data nella clausola WHERE. Questo migliora le prestazioni delle query limitando la query a uno o pochi archivi di dati più piccoli.

Organizzando una tabella di Database di grandi dimensioni

Che è ancora un tavolo massiccio, avendo 500 milioni di righe e consumando 60 GB di storage. Una tabella ordinaria che grande sarebbe lenta ad aggiornare e lento alla query. Aggiunta di indici può aiutare, ma hanno ulteriormente rallentare gli aggiornamenti. Come tabelle diventano più grandi, crea strati multipli di indici (profondità di indice), che riduce la loro disponibilità.

Cubi OLAP multidimensionali sono un approccio per l'interrogazione di grandi quantità di dati, ma richiedono un sacco di progettazione e pianificazione prima della distribuzione. E una volta distribuito, non è possibile aggiornarli. Cubi OLAP vengono ricostruiti in genere ogni giorno, o anche meno frequentemente.

Il mio approccio alla gestione di questo tavolo massiccio combina due tecniche: partizionamento e indicizzazione di columnstore. Una limitazione significativa delle tabelle columnstore indicizzati in SQL Server 2012 è che non è possibile aggiornarli mentre l'indice è attivo. È necessario disattivare l'indice durante l'aggiornamento e ricostruirlo completamente dopo l'aggiornamento. Questo è un processo ingombrante quando si tratta di un grande tavolo. Descriverò una tecnica per superare questa limitazione. Queste restrizioni sono state rilassate in SQL Server 2014 (vedi "Partizionamento di una tabella SQL Server ).

I vari formati di partizione, mese e giorno e ora — corrispondono ai modelli comuni di query degli utenti business. Essi corrispondono anche i modelli comuni di aggiornamento e manutenzione. L'applicazione potrebbe essere necessario partizione di diverse dimensioni e valori diversi da "data" più utili per il partizionamento. In altre applicazioni, potrebbe essere meglio per mantenere le partizioni appena orarie o giornaliere. SQL Server 2012 supporta partizioni fino a 15.000 per ogni tabella, permettendo partizioni orarie per 20 mesi. Consultare la documentazione del SQL Server a bit.ly/1mtZkfl per una spiegazione più autorevole e approfondita di partizionamento.

Indici columnstore

La novità più eccitante SQL Server 2012 introdotta è indici columnstore. Sono un potente strumento per migliorare le prestazioni delle query. Per le tabelle massicce come la mia, ho visto una performance 100 x aumentare.

Il grave problema in SQL Server 2012 è una tabella indicizzata columnstore, come un cubo OLAP, è di sola lettura. Lei non potrà aggiornarlo senza cadere (o almeno la disattivazione) l'indice e ricostruirlo dopo l'aggiornamento. Dopo aver verificato i miglioramenti di prestazioni, ho cercato un modo superare il limite di sola lettura e partizionamento trovato è stata la risposta.

Mentre penso di partizionamento come affettare un tavolo a fette orizzontali (basati sul tempo nel mio caso), penso di indici columnstore come affettare un tavolo a fette verticali di colonna. Come tutti gli indici non cluster, l'indice viene archiviato separatamente dai dati della tabella principale. Ogni colonna indicizzata columnstore è memorizzato individualmente nel proprio contenitore interno blob. Questo è molto diverso da un indice composito con più colonne indicizzate o incluse insieme.

Perché la tabella di base è di sola lettura, dati i BLOB columnstore non occorre gestire aggiornamenti ed essi possono essere compressi utilizzando uno dei diversi algoritmi scelti dal SQL Server per abbinare i dati. Quindi l'indice è spesso sostanzialmente più piccolo di dati originali (vedere Figura 1).

Figura 1 utilizzo indici Columnstore per gestire grandi archivi di dati

Il mio indice columnstore include ogni colonna della tabella. Ogni colonna viene archiviato separatamente. Una query che fa riferimento solo alcune colonne leggerà solo quelle colonne e ignora il resto. Le query non necessario leggere le righe nella tabella di base. Tutti i valori di dati sono nell'indice e i dati per ogni colonna dell'indice sono compressa per minimizzare letture del disco.

Ricordate la mia tabella è partizionata anche, così ogni partizione e ogni colonna nella partizione ha un proprio spazio di archiviazione (vedere Figura 2). SQL Server è abbastanza intelligente per leggere solo i dati che ha bisogno (partizioni e colonne) per una specifica query con miglioramento del tempo-risposta alla query impressionante rispetto alla scansione di una tabella "normale" riga per riga, o anche la ricerca attraverso indici normali per i dati necessari.

Figura 2 utilizzo di partizioni e indici Columnstore per analizzare i dati

Un importante effetto collaterale positivo di tutto questo è non non c'è nessuna penalizzazione delle prestazioni per denormalizzazione della tabella. L'implementazione ex sofferto perché le query contro il precursore alla tabella entrate spesso incluso si unisce alle altre tabelle per ottenere gli attributi secondari. A quel tempo, ho voluto tenere le righe della tabella magro ed evitare la lettura dei dati non necessari dal disco ogni volta il grande tavolo è stato interrogato.

A volte l'unisce causato SQL Server eseguire una scansione completa della tabella per una query, soprattutto quando l'attributo secondario era nella clausola WHERE. Ora, molti di quegli attributi secondari (e alcuni terziari) sono archiviati nella tabella molto più ampia delle entrate sapendo che con columnstore indicizzazione, essi non sono accessibili se non sono necessari di tale query specifiche. La tabella delle entrate che io ho progettato ha 25 colonne. Consultare la documentazione del SQL Server a bit.ly/1zbsju1 per più autorevole e approfondita comprensione degli indici columnstore.

Aggiornamento

La combinazione di partizionamento e indicizzazione di columnstore ha rotto il tavolo massiccio in segmenti di modeste dimensioni gestibili. Tuttavia, a causa dell'indice columnstore, gli aggiornamenti sono proibiti in SQL Server 2012. Anche se ogni partizione viene archiviati separatamente, essi stanno tutti trattati come una tabella, quindi disabilitando, far cadere o ricostruzione dell'indice coinvolge l'intera tabella. Ricostruzione dell'indice columnstore può prendere una mezz'ora e la tabella non è disponibile per le query durante quel tempo. La tabella deve essere aggiornato con nuovi dati ogni 10 minuti ed essere disponibile tutto il tempo.

La chiave per risolvere questo problema è una dichiarazione relative partizioni: ALTER TABLE... SWITCH PARTITION... Questa istruzione DDL sposta una partizione di dati da una tabella a altra. Non copia i dati, ma solo riorganizza informazioni sullo schema interno così l'archiviazione di partizioni che apparteneva a una tabella ora appartiene a una tabella diversa. Ci sono alcune regole che disciplinano questo processo, ma sono gestibili. Leggi l'articolo di TechNet Library, "Trasferimento di dati in modo efficiente di utilizzando lo spostamento di partizioni" (bit.ly/1ts04Xv), per una descrizione approfondita delle partizioni.

Cerchiamo di rivisitare il problema originale. C'è un torrente di dati (20.000 operazioni al secondo) dall'annuncio che servono macchine andando Hadoop 24x7. Posso usare Hadoop per riepilogare i dati di ora corrente a circa 50.000 righe SQL Server per quell'ora. Che Hadoop query richiede circa 5 minuti. Io non posso aggiornare il mio tavolo entrate principale, ma posso mettere quelle righe in una tabella nuova (partizionato e columnstore indicizzati) chiamare RevenueIn. Ha lo stesso schema della tabella delle entrate, ma è vuota e relativo indice columnstore è disattivato, quindi posso inserire righe.

Dopo l'inserimento di righe di un'ora, io posso ricostruire l'indice columnstore della tabella RevenueIn. Dati dell'ora si inserisce appunto in una partizione. Io sto solo l'indicizzazione 50.000 righe, così che richiede meno di un minuto. Ora posso usare SWITCH PARTITION per spostare quella uno partizione, già indicizzati, nelle entrate principali tabella e si di immediatamente disponibili per le query di relazione. Si noti che l'istruzione SWITCH PARTITION funziona solo se la partizione di destinazione è vuota. Per risolvere il problema, utilizzare una terza tabella, chiamata RevenueOut, che è vuota.

Dati esistenti dalla partizione delle entrate sono passati a RevenueOut (la partizione di entrate lasciando vuoto) e quindi la partizione RevenueIn (con i nuovi dati e il relativo indice) è commutata in entrate. Le due istruzioni SWITCH prendono meno di 5 ms nel mio ambiente. I vecchi dati in RevenueOut poi viene troncati (vedere Figura 3). Questa è una variazione sul problema partizione finestra scorrevole descritto a bit.ly/1wgPVkR.

Figura 3 rinfrescante dati di una partizione

Dettagli, dettagli

Ci sono sempre i dettagli. L'applicazione si ripete questo processo sei volte o più all'ora (più veloce che può eseguire query Hadoop). All'inizio dell'ora, ci sono solo pochi minuti di dati e ogni aggiornamento durante l'ora che sostituisce con nuovi dati. Poco dopo la cima dell'ora, c'è un'intera ora di dati nella tabella entrate enorme e può iniziare l'ora successiva. L'utente ha il lusso di dati correnti, freschi e più di storia di un anno a sua portata di mano in una tabella che è facile e veloce alla query.

Gli sviluppatori di vincere anche con una singola tabella di database. La soluzione precedente utilizzata una tabella per dati "attuali" e un'altra per i dati storici. Codice c# ha dovuto decidere quali tabelle da usare e combinare i risultati di query su database, se una query utilizzate entrambe le tabelle. La singola tabella e minimo utilizzo di join (a causa di colonne denormalizzate abilitato senza pena di indicizzazione columnstore) semplifica la query di database per la segnalazione.

Un altro dettaglio è la fusione. Mantenere dati meno recenti in partizioni mensile. Ciò semplifica la manutenzione come posso cadere un mese che va oltre i requisiti. Partizioni meno semplificano anche la logica interna del SQL Server per selezionare le partizioni per le query, come la maggior parte delle query per vecchi dati includono mesi interi. L'indice columnstore guadagna anche qualche efficienza con partizioni più grandi a causa della sua compressione algo­ritmiche. Io uso una tecnica simile all'inserimento di dati per arrotolando piccole partizioni in grandi partizioni senza ricostruire i dati.

C'è qualche idraulico SQL Server coinvolti. Un articolo di accompagnamento online presso msdn.microsoft.com/magazine/dn800596 fornisce istruzioni dettagliate e T-SQL di esempio per tutti i necessari oggetti di database SQL Server . Controllare che l'articolo e copiare il codice da lì.

---

Don Mackenzie è direttore dell'architettura software presso Cox Digital Solutions, la pubblicità del braccio di Cox Media Group e Cox imprese Internet. Egli gode di applicare nuove tecnologie alle applicazioni business. Contattarlo al don@coxds.com.

Grazie al seguente Microsoft esperto tecnico per la revisione di questo articolo: Remus Rusanu