Datenbanktabellen mit CSV extrahieren [ODBC-Verbindung von R und Python]

Was macht ihr, wenn ihr Datenbankdaten mit CSV extrahieren wollt? Bitte erzähl mir davon, wenn es dir nichts ausmacht! Ich sehe so aus ↓ ↓ ↓

Ich möchte die Datenbanktabelle CSV machen

Ich möchte das Ergebnis einer Extraktionsanforderung in die Datenbank konvertieren, indem ich eine Abfrage in CSV schreibe.

Interne Datenanalysten schreiben SQL und erstellen Dateien für die Analyse. Es ist in Ordnung, mit Excel zusammenzubauen und eine Verbindung zu ODBC herzustellen, aber ich möchte die Daten einfach in die Statistiksoftware einfügen. Ich möchte die numerische Berechnungslogik und die Ersetzungslogik so verarbeiten, wie sie sich auf der Seite der Analysesoftware befinden.

Verbinden Sie sich mit ODBC

Es gibt etwas namens odbc, das die Verbindung mit der Datenbank vermittelt. Lassen wir die ODBC-Einstellungen und was mit anderen Sites zu tun ist. Wenn Sie nicht derjenige sind, der die Datenbank selbst einrichtet, sollte jemand sie verwenden, fragen Sie also. Fragen Sie vorerst einfach nach der IP-Adresse des Datenbankservers und des Treibers für die Verbindung.

Die Methode ist ein Link. Hier oder ODBC Dies ist auch ODBC

Stellen Sie zum Zeitpunkt der Einstellung "Datenquellenname" ein. Notieren Sie sich dies.

Zuerst aus der R-Sprache

Sie können R installieren, indem Sie es von CRAN herunterladen, installieren und das folgende Skript in den R-Editor eingeben.

Angenommene Tabelle und Skript ohne Unebenheiten

Angenommen, Sie haben eine Tabelle mit 6 Millionen Datensätzen mit dem Namen user_log Es dauert enorm lange, alles mit Software zu extrahieren, die normalerweise eine Verbindung zur Datenbank herstellt. Wenn auch andere Personen verbunden sind, ist es wichtig, dass die CPU-Auslastung des Servers unheimlich wird und alle wütend werden.

Für Software, die eine Verbindung zur Datenbank herstellt, gefällt mir → CSE. Dies kann auch verbunden werden, wenn der Name, die ID und der Pass der ODBC-Datenquelle bekannt sind.

Grundsätzlich diese Zusammensetzung

library(RODBC)

conn_DB <- odbcConnect("Name der Datenquelle", "Zugewiesene ID", "Pass, der mit der ID ausgestellt wird")
query <- paste0("select count(*) from user_log")
tbl <- sqlQuery(conn_DB, query)
odbcClose(conn_DB)

Drehen Sie dies mit einer FOR-Anweisung.

Zuerst können Sie eine Verbindung zur obigen Datenbank herstellen, um zu sehen, wie viele Zeilen die Tabelle enthält. Angenommen, Sie können bestätigen, dass es 6 Millionen sind. Wenn Sie versuchen, alles auf einmal herauszuziehen, hört die CPU des Servers auf zu denken. Ziehen Sie also in kleinem Maßstab durch Teilen heraus. Auch auf der Seite der Analysesoftware besteht die Möglichkeit, dass der Speicher zugeschlagen wird. Es wird daher empfohlen, ihn detailliert zu schreiben und am Ende zu kombinieren.

SEQ<-seq(1,6000000,5000)
LIM<-length(SEQ)-1

conn_DB <- odbcConnect("Name der Datenquelle", "Zugewiesene ID", "Pass, der mit der ID ausgestellt wird")
for(i in 1:LIM){
  query <- paste0(
    "select * from user_log ",
    "limit 5000 offset ",
      as.numeric(SEQ[i])
  )
  tbl <- sqlQuery(conn_DB, query)
  write.csv(tbl, paste0("this_is_",i,"th.csv"), row.names=F)
  Sys.sleep(10)
}
odbcClose(conn_DB)

Detaillierte Erklärung und Vorsicht

Damit könnten die Daten extrahiert werden, indem sie in 5000 Datensätze aufgeteilt werden. Sie können in SQL geschriebene Abfragen mit paste0 kombinieren. Wenn es in SQL ein einfaches Anführungszeichen gibt, stellen Sie ihm einen Backslash voran.

where colum = '10'

where colum = \'10\'

Wenn Sie Zeichen mit paste0 kombinieren, verschwinden möglicherweise die SQL-Zeilenumbrüche. Seien Sie also beim Schreiben vorsichtig.

paste0("select * from user_log", "where colum = '10'")

select * from user_logwhere colum = '10'

Es ist zusammengeklebt.

Kombinieren Sie die extrahierten Daten

setwd("Pfad der Datei, die csv ausspuckt")
lf <- list.files(pattern="csv") 

data <- data.frame()

  for(i in 1:length(lf)){
    add <- fread(lf[i])
    data <- rbind(data,add)
  }

Dadurch werden 6 Millionen Daten in Daten gespeichert. Bitte beachten Sie, dass die Verarbeitung katastrophal ist, wenn der PC nicht über genügend Speicher verfügt.

Ist es nicht in Ordnung, jede CSV für das maschinelle Lernen einem Mini-Batch gleichzusetzen?

Python-Sprachausgabe

Da ich die Richtlinien bis zu einem gewissen Grad erläutert habe, werde ich die Erklärung von nun an weglassen.

import pyodbc
import numpy as np
import pandas as pd

cnx = pyodbc.connect('DSN=Name der Datenquelle; UID=ID eingeben; PWD=Pass einfügen')
cursor = cnx.cursor()

list_for = np.arange(1,60000,5000)
list_for=list_for.tolist()

for i in range(len(list_for)):    
    made_que = "SELECT * FROM user_log " + "LIMIT 5000 OFFSET " + str(list_for[i])
    cursor.execute(made_que) 
    tbl = cursor.fetchall()
    array_format = np.array(tbl)
    dataframe_format = pd.DataFrame(array_format)
    dataframe_format.to_csv('csv_data/' + str(i) + '.csv')

das ist alles

Analyse Produktion nach Extraktion