Schreiben einer Create Table-Anweisung mit Python und Laden eines CSV in Redshift

Normalerweise, wenn ich ein CSV hochladen muss, werde ich die CSV-Funktion von Periscope Data verwenden. Es ist schnell, einfach, erlaubt es mir, die Daten mit allen meinen Datenbanken zu verbinden und gießt automatisch Typen.

Manchmal interagiere ich jedoch gerne direkt mit einem Redshift-Cluster – meist für komplexe Datentransformationen und Modellierung in Python. Wenn Sie direkt mit einer Datenbank interagieren, kann es schwierig sein, eine create

table-Anweisung zu schreiben und Ihre Daten zu laden. Wenn die Tabelle breit ist, haben Sie beim Schreiben Ihrer Kreiertabelle zwei Möglichkeiten – verbringen Sie die Zeit damit, die richtigen Datentypen herauszufinden, oder importieren

Sie alles langsam als Text und befassen Sie sich mit dem Typ Casting in SQL. Das erste ist langsam, und das zweite wird dich in Schwierigkeiten bringen.

Ich bin kürzlich auf ein großartiges Beispiel dafür gestoßen, als ich herausfand, dass Stack Overflow ihre fantastischen Umfrageergebnisse für 2017 veröffentlicht hat. Sie sind im CSV-Format verfügbar, aber mit einer beeindruckenden

Breite von 158 Spalten. Ich wollte die Daten in Redshift laden – und anstatt großzügig in meinen Datentypen zu sein, wollte ich die richtigen Spalten verwenden. Ich entschied mich, den Ladevorgang zu beschleunigen, indem ich ein

Python-Skript schrieb um eine Python Tabelle erstellen zu können, wassich zu einer lustigen Übung in der Datentyperkennung entwickelte.

Bibliotheken importieren und Daten in Python lesen

Der erste Schritt besteht darin, unsere Daten zu laden, unsere Bibliotheken zu importieren und die Daten in ein CSV-Leserobjekt zu laden. Die CSV-Bibliothek wird verwendet, um über die Daten zu iterieren, und die Ast-Bibliothek wird verwendet, um den Datentyp zu bestimmen.

 

Wir werden auch einige Listen verwenden. „Longest“ ist eine Liste der längsten Werte in der Zeichenlänge, um die Kapazität der Varchar-Spalte anzugeben, „Header“ ist eine Liste der Spaltennamen und „type_list“ ist die aktualisierte Liste der Spaltentypen, die wir über unsere Daten wiederholen.

csv, ast, psycopg2 importieren

f = open(‚/path/to/survey/data/survey_data.csv‘, ‚r‘)

Leser = csv.reader(f)

am längsten, Überschriften, Typ_Liste = [], [], [], [], []

Auffinden des Datentyps

Sobald wir unsere Daten haben, müssen wir den Datentyp für jede Zeile finden. Das bedeutet, dass wir jeden Wert bewerten und auf die restriktivste Option setzen müssen, von dezimalisierten Zahlen bis zu ganzen Zahlen und von ganzen Zahlen bis zu Zeichenketten.

Dies geschieht mit der Funktion dataType. Zuerst wird geprüft, ob es sich bei dem Wert um Text oder eine Zahl handelt, und dann bei Bedarf um die entsprechende Art von Zahl. Diese Funktion verbraucht sowohl die neuen Daten als auch den aktuell besten Typ, gegen den Sie auswerten können.

def dataType(val, current_type):

versuche es:

# Bewertet Zahlen zu einem geeigneten Typ und gibt einen Fehler zurück.

       t = ast.literal_eval(val)

   außer ValueError:

       gibt’varchar‘ zurück

   außer SyntaxError:

       gibt’varchar‘ zurück

   wenn type(t) in [int, long, float]:

       if (type(t) in [int, long]) und current_type nicht in [‚float‘, ‚varchar‘]:

           # Verwenden Sie den kleinstmöglichen int Typ

           if (-32768 < t < 32767) und current_type nicht in [‚int‘, ‚bigint‘]:

               return’smallint‘ zurückgeben

           elif (-2147483648 < t < 2147483647) und current_type nicht in [‚bigint‘]:

               zurückgeben „int

           sonst:

               return ‚bigint‘ zurückgeben

     wenn type(t) float und current_type nicht in [‚varchar‘] ist:

           gibt’dezimal‘ zurück

sonst:

  gibt’varchar‘ zurück

Wir können über die Zeilen in unserem CSV iterieren, unsere Funktion oben aufrufen und unsere Listen füllen.

für die Zeile im Leser:

   if len(headers) == 0:

       Überschriften = Zeile

       für Spalte in Zeile:

           längste.append(0)

           type_list.append(“‘)

   sonst:

       für i in range(len(row)):

           # NA ist der csv-Nullwert.

           wenn type_list[i] ==’varchar‘ oder row[i] ==’NA‘:

               Durchlauf

           sonst:

               var_type = dataType(row[i], type_list[i])

               typ_liste[i] = var_type

       wenn len(row[i]) > längster[i]:

           längste[i] = len(row[i])

f.close()

 

Und verwenden Sie unsere Listen, um die SQL-Anweisung zu schreiben.

Anweisung =’create table stack_overflow_survey (‚)

für i im Bereich(len(headers)):

   if type_list[i] ==’varchar‘:

       Anweisung = (Anweisung +’\n{} varchar({}),‘).format(headers[i].lower(), str(longest[i])))

   sonst:

       statement = (statement + ‚\n‘ + ‚{} {}‘ + ‚,‘).format(headers[i].lower(), type_list[i])

statement = statement[:-1] + ‚);‘;‘

Endlich, unser Output!

Erstellen der Tabelle stack_overflow_survey_data (

   Befragte int,

   , professioneller Varchar(56)

   Programmhobby varchar(45)

   Land varchar(34)

   ….

   , erwartete geldliche Dezimalstelle);

Beenden des Auftrags

Natürlich ist der Job nicht erledigt – die Daten müssen in Redshift kommen! Dies kann mit Hilfe der psycopg2-Bibliothek geschehen, und der schlaue Leser wird feststellen, dass wir es oben importiert haben. Um den Kopierbefehl zu verwenden, habe ich zuerst die Daten nach S3 geladen. Die Zugangsschlüssel-ID und der geheime Zugangsschlüssel finden Sie unter den Benutzern in Ihrer AWS-Konsole.