Trasferire Dati da MS SQL Server a MongoDB

Lo sviluppo di MongoDB [link] come database NoSQL accelera ogni giorno e il trasferimento dati da realtà SQL si fà sempre più ncessario.

OBIETTIVO:

Trasferire giornalmente quasi 50 milioni di righe da un database MS SQL Server 2005 ad un database NoSQL MongoDB in modalità bulk-copy o alternativa veloce.

PROBLEMA:

Non esiste (che io sappia) un importare diretto, bisogna quindi procedere in 2 tempi tramite file esterno.

SOLUZIONE:

Fase 1 – Esportare Dati da MS SQL Server 2005 in file CSV in modo automatico tramite BCP utility di Microsoft. La procedura è automatizzata tramite scheduling di file BAT [vedi post precedenti]

Fase 2 – Importare Dati da file CSV in MongoDB tramite MONGOIMPORT utility di Mongo. La procedura è automatizzata tramite scheduling di file BAT [vedi post precedenti]

ATTENZIONE:

Per il trasferimento di databases da un server di MongoDB ad un altro viene consigliato di utilizzare il comando copydb [link].

Annunci

Utilizzare MONGOIMPORT utility per importare dati da un file CSV in un database MongoDB

Si è trattato nei post precedenti dell’esportazione dati da database SQL a file CSV in modalità bulk-copy [link]. Vediamo come fare per importare velocemente da un file CSV i dati all’interno di un database MongoDB utilizzando la utility mongoimport.exe presente nella cartella \mongodb\bin per Windows.

OBIETTIVO:

Importare un file CSV di dati (senza header) all’interno di un database MongoDB.

SOLUZIONE:

Utilizzare la funzione mongoimport -d database.name -c database.collection_name –type csv –file c:\myfile.csv -f my_id, my_date, my_value –upsert –upsertFields my_id[link].

ATTENZIONE: 

Per avviare mongoimport.exe bisogna entrare nella directory dove si trova il file. Quindi se ad esempio ci si trova in c:\user\Max bisogna prima fare CD c:\mongo\bin, quindi eseguire MONGOIMPORT.

ESEMPIO:

Creare un file BAT del tipo:

@ECHO OFF

CD c:\mongo\bin

mongoimport -d database.name -c database.collection_name –type csv –file c:\myfile.csv -f my_id, my_date, my_value –upsert –upsertFields my_id

Creare un file LOG di procedure eseguite tramite un BAT file

Creare un file di testo per il controllo dell’esecuzione di processi da command-line può essere molto utile. Invece che stampare a video, si può registrare l’esecuzione di una procedura in un file .txt o .log da analizzare successivamente.

OBIETTIVO:

Creare un file .log con commenti, data e tempo iniziale, stampa di alcune procedure, tempo finale. Al nome del file verrà concatenata una stringa della Data e del Tempo.

SOLUZIONE:

Utilizzare i simboli maggiore [>]  e molto maggiore [>] di DOS [link]: il primo sovrascrive il file ogni volta, il secondo aggiunge una riga al file (append).

ESEMPIO:

Creare un BAT file del tipo:

@echo off
:: export_values_from_mssqldb.bat
::
:: Esporta i dati dal database MS SQL Server 2005.
:: Imposta il file LOG nella forma [mssqlreport_yyyymmddHHMMSS.log]
::
:: set variables
SETLOCAL
SET logpath=c:\root\mmdata_temp\log\
SET logdate=%date:~6,4%%date:~3,2%%date:~0,2%
SET logtime=%time:~0,2%%time:~3,2%%time:~6,2%
SET mylogfile=%logpath%mssqlexport_%logdate%%logtime%.log

:: create file log
ECHO Export MS SQL Server  data to CSV > %mylogfile%
ECHO %date% >> %mylogfile%
ECHO %time% >> %mylogfile%
:: export data_values.csv
BCP “exec mydb.dbo.exportDataToCSV” queryout c:\data_temp\data_values.csv -c -t , -U userid -P pw -S myserver\mssql_instance >> %mylogfile%

:: copia file su \server

:: set overwrite
SET COPYCMD=/Y >> %mylogfile%
:: copy data_values.csv
XCOPY c:\data_temp\data_values.csv \\server\data_temp >> %mylogfile%

:: fine dell’esecuzione
ECHO %time% >> %mylogfile%

Il risultato sarà del tipo:

Export MS SQL Server  data to CSV

26/04/2012
16:55:48,09

Starting copy…
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000

676739 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1825 Average : (370815.89 rows per sec.)

C:\data_temp\data_values.csv
1 File copiati

16:55:50,35

Creare chiavi composte tramite Join di Viste e Tabelle in MS Sql Server

La selezione di colonne/righe in SQL  tramite l’unione (JOIN) di 2 tabelle è semplice. Problema più difficoltoso è quello di unire 3 o più tabelle, evitando JOIN annidati e volendo utilizzare il risultato di una unione per costruire una chiave complessa.

OBIETTIVO:

Date 3 tabelle nominate TAB1,TAB2,TAB3 effettuare un JOIN delle 3 tabelle estrapolando solo alcune colonne comuni a tutte e tre le tabelle, modificando alcuni risultati di una tabella in stringhe personalizzate.

PROBLEMA:

Il risultato della TAB1  è una array di stringhe che deve essere convertita in stringhe del tipo (‘pippo’=’A’, ‘pluto’=’B’, ‘paperino’=’D’). Tali stringhe faranno parte di una chiave composta da associare ad alcuni campi comuni di TAB2 e TAB3. L’array è formato da 4 stringhe, ma a noi ne interessano solo 3.

SOLUZIONE:

Si crea una View della tabella  TAB1 (es. VIEW1), quindi in un SELECT successivo si uniscono  TAB1,TAB2 e TAB3 del tipo [… FROM TAB1 JOIN TAB2 ON TAB1.id=TAB2.id JOIN VIEW1 ON TAB1.id=VIEW1.id]

In pratica la Vista sostituisce la creazione di una Tabella, utile in questo caso per la manipolazione di Dati fissi o statici.

ESEMPIO:

A –  Creare la VIEW1

CREATE VIEW View1

AS

SELECT newCol =

CASE Area

WHEN ‘pippo’ THEN ‘A’

WHEN ‘pluto’ THEN ‘B’

WHEN ‘paperino’ THEN ‘C’

ELSE ‘D’

END

FROM TAB1 WHERE myid = 1 OR myid  = 2 OR myid = 4

2 – Crea la procedura completa 

CREATE PROC multijoin

AS

SELECT newCol + ‘_id  AS col1_id,

Tab2_name AS col2_name,

Tab3_value AS col3_value

FROM TAB1 JOIN TAB2 ON Tab1_id = Tab2_id JOIN VIEW1 ON Tab1_id = View1_id

 

 

Creare un file BAT per Automatizzare l’Esportazione di Dati in un file CSV da un database MS Sql Server

Terzo appuntamento con la procedura di esportazione dei dati di una Tabella in MS Sql Server in un file CSV mediante l’utilità Bulk-Copy (bcp.exe).

OBIETTIVO:

Automatizzare la procedura di esportazione dal database al file CSV e successivo scheduling.

SOLUZIONE:

Creare un file .BAT tramite un editor di testo (es. notepad.exe) [link], utilizzando i comandi DOS disponibili [link]. Il file BAT esegue i comandi una riga per volta emulando la digitazione manuale.

ESEMPIO:

In notepad creare un file auto_exp_csv_test.bat. Inserire del codice tipo:

ECHO OFF

TITLE Export Data to CSV

ECHO Test di esportazione automatica di dati da una tabella in MS SQL Server

ECHO Chiama BCP utility

BCP database_name.schema.table_name out c:\output_file.csv -c -t , -U login_id  -P password -Sserver_name\instance_name

ECHO Fine Esportazione

PAUSE

La funzione PAUSE mette in pausa sino alla digitazione di un tasto. E’ stata inserita solo per fare rimanere attiva la shell di Windows temporaneamente e controllare l’esecuzione del codice. In fase di scheduling verrà eliminata.

Export Dati da una Tabella di MS SQL Server in un file CSV usando BCP utility e Stored Procedures

Approfondimento del post relativo all’esportazione  in blocco di dati da un database MS SQL Server in un file di  CSV [link].

OBIETTIVO:

Selezionare solo alcune colonne delle tabelle, creare una chiave stringa ad-hoc ed esportare in blocco in modalità Bulk-Copy (bcp.exe).

PROBLEMA:

Registrare una Store Procedure con query interna del tipo [SELECT… JOIN…ON], esecuzione della BCP utility da shell di comando attraverso l’estensione xp_cmdshell.  La procedura lanciata per ca. 42 milioni di righe con una query composta va in eccezione System.OutOfMemoryException nonostante il sistema sia x64 con 12GB di ram.

SOLUZIONE:

Creazione di Store Procedure su MS SQL Server ed esecuzione di BCP utility da Terminale Windows (cmd.exe).

ESEMPIO:

Store Procedure + BCP interno [link]

USE MYDB
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC exportDataToCSV

AS

SET NOCOUNT ON

SELECT

Tab1_data1, Tab2_data2 + ‘TEST’ + CAST(Data_ID AS VARCHAR(100)) AD Test_ID,

Tab_Date  AS Test_Date,

Tab_Level as Test_Value,

FROM

TAB1 AS T1

JOIN

TAB2 AS T2

ON

T1.Data_ID = T2.Level_DataID

/*Set BCP utility string */

DECLARE @SQL VARCHAR(8000)
SELECT @SQL = ‘BCP “EXEC MYDB.dbo.exportDataToCSV” queryout c:\DATA.csv -c -t , -U loginid -P password -S nome_server\nome_istanza’

/*Esecuzione Comando*/

EXEC master..xp_cmdshell @SQL

Store Procedure + BCP esterno

Si registra la procedura exportDataToCSV, quindi si apre il terminale e si esegue:

BCP “EXEC MYDB.dbo.exportDataToCSV” queryout c:\DATA.csv -c -t , -U loginid -P password -S nome_server\nome_istanza

Per fare funziona la BCP utility internamente occorre attivare la funzione xp_cmdshell [link].

Conversione del Numero Seriale di una Data per Matlab (windows) ed Excel (windows/macosx) in standard UNIX

Il Tempo è rappresentato nel sistema Unix/Posix  dal numero di secondi passati dalle ore 00:00 UTC (Universal Time Coordinated) di giovedì 1 Gennaio 1970 [link].

Matlab ed Excel  forniscono 3 differenti valori iniziali:

  • Matlab: data di partenza con numero seriale calcolato al 1 Gennaio 0000 (anno zero) [link].
  • Excel per Windows: data di partenza con numero seriale calcolato al 1 Gennaio 1900 [link].
  • Excel per Mac OX:  data di partenza con numero seriale calcolato al  1 Gennaio 1904 [link].

In tutti e tre i casi la progressione numerica è unitaria (espressa in giorni).

OBIETTIVO:

Trasformare i tre formati menzionati nello standard Unix.

SOLUZIONE:

Un giorno è composto da 60 (sec) * 60 (min) * 24 (ore) = 86.400 (sec).

  • Unix: [differenza tra T (giorno di valutazione) e Ts (data di partenza)] * 86.400 (sec)
  • Matlab: [differenza tra T (giorno di valutazione) e Ts (1 gen 1970)] * 86.400 (sec)
  • Excel:  [differenza tra T (giorno di valutazione) e Ts (1 gen 1970)] * 86.400 (sec)

ESEMPIO:

La data 19-Aprile-2012 alle ore 00:00 si calcola:

  • Unix: 15.449  * 86.400 = 1.334.793.600
  • Matlab:  [datenum(’19-4-2012′,’dd-mm-yyyy’) – datenum(’01-01-1970′,’dd-mm-yyyy’)] * 86.400 = 1.334.793.600
  • Excel: (DATEVALUE(“19/4/2012”) – DATEVALUE(“01/01/1970”)) * 86.400 = 1.334.793.600

Ricordando che il 01-01-1970 è il numero seriale:

  • Matlab: 719.529
  • Excel per Windows: 25.569
  • Excel per MacOSX: 24.107

Passare da una soluzione all’altra utilizzando il differenziale è facile.