Ecrire un script pour convertir un vidage MySQL en TSV

Tente de convertir le SQL sauvegardé de MySQL en texte délimité par des tabulations (appelé TSV) à d'autres fins.

Aperçu

Les tables MySQL peuvent être vidées en SQL avec mysqldump. Les parties principales sont les instructions CREATE et INSERT.

• Vider en spécifiant une table | manuel mysqldump

(Omis) CREATE TABLE t_price ( (Omis) INSERT INTO t_price VALUES (1,'forme fixe',25,82),(Omis)(10,'forme fixe外',4000,1180); (Omis)

Vous pouvez effectuer un vidage directement sur TSV s'il s'agit d'un vidage de MySQL que vous gérez.

• Vider les données MySQL au format csv, tsv --Qiita

Si les seules données publiées ailleurs sont un vidage SQL, vous pouvez soit le récupérer et le vider, soit le convertir. Cette fois, je vais écrire un script et essayer la conversion.

• Comme cela sera décrit plus loin, il est supposé que le vidage du Wiktionnaire sera converti. Étant donné que les données sont énormes, je ne veux pas les importer une seule fois.

spécification

Extrait uniquement les valeurs de l'instruction INSERT de SQL et les convertit en TSV. Plus précisément, nous visons le comportement suivant.

Avant la conversion

INSERT INTO `table` VALUES (1,2,3),(4,5,6);
INSERT INTO `table` VALUES (1,'a,b','c\'d');

Après la conversion

1	2	3
4	5	6
1	a,b	c'd

Python

Écrivez un script de conversion en Python.

read_string

Analyse la chaîne entourée du guillemet simple `` ''. C'est le plus fréquenté.

Vérifie si le premier caractère est `` ''. Le traitement des séquences d'échappement est simplifié en supprimant \ pour \" , \ ',\\, mais en laissant\pour les autres caractères de contrôle.

test est un indicateur de test.

sql2tsv.py

test = True

def read_string(src, pos):
    length = len(src)
    if pos >= length or src[pos] != "'": return None
    pos += 1
    ret = ""
    while pos < length and src[pos] != "'":
        if src[pos] == "\\":
            pos += 1
            end = pos >= length
            if end or not src[pos] in "\"'\\":
                ret += "\\"
            if end: break
        ret += src[pos]
        pos += 1
    if pos < length and src[pos] == "'": pos += 1
    return (ret, pos)

if test:
    src = r"'a,b','c\'d'"
    pos = 0
    while pos < len(src):
        s, p = read_string(src, pos)
        print("read_string", (src, pos), "->", (s, p))
        pos = p + 1

Résultat d'exécution

read_string ("'a,b','c\\'d'", 0) -> ('a,b', 5)
read_string ("'a,b','c\\'d'", 6) -> ("c'd", 12)

read_value

Lisez une donnée. S'il s'agit d'une chaîne, lisez read_string, sinon lisez vers, ʻou (`.

sql2tsv.py (suite)

def read_value(src, pos):
    length = len(src)
    if pos >= length: return None
    sp = read_string(src, pos)
    if sp: return sp
    p = pos
    while p < length and src[p] != "," and src[p] != ")":
        p += 1
    return (src[pos:p], p)

if test:
    for src in ["1,2,3", r"1,'a,b','c\'d'"]:
        pos = 0
        while (value := read_value(src, pos)):
            s, p = value
            print("read_value", (src, pos), "->", (s, p))
            pos = p + 1

Résultat d'exécution

read_value ('1,2,3', 0) -> ('1', 1)
read_value ('1,2,3', 2) -> ('2', 3)
read_value ('1,2,3', 4) -> ('3', 5)
read_value ("1,'a,b','c\\'d'", 0) -> ('1', 1)
read_value ("1,'a,b','c\\'d'", 2) -> ('a,b', 7)
read_value ("1,'a,b','c\\'d'", 8) -> ("c'd", 14)

read_values

Lit toutes les données séparées par des virgules entre parenthèses.

sql2tsv.py (suite)

def read_values(src, pos):
    length = len(src)
    if pos >= length or src[pos] != "(": return None
    pos += 1
    ret = []
    if pos < length and src[pos] != ")":
        while (value := read_value(src, pos)):
            s, pos = value
            ret.append(s)
            if pos >= length or src[pos] != ",": break
            pos += 1
    if pos < length and src[pos] == ")": pos += 1
    return (ret, pos)

if test:
    for src in [r"(1,2,3)", r"(1,'a,b','c\'d')"]:
        print("read_values", (src, 0), "->", read_values(src, 0))

Résultat d'exécution

read_values ('(1,2,3)', 0) -> (['1', '2', '3'], 7)
read_values ("(1,'a,b','c\\'d')", 0) -> (['1', 'a,b', "c'd"], 16)

read_all_values

Lit toutes les données entre parenthèses. Je vais en faire un générateur en supposant une manipulation en boucle.

sql2tsv.py (suite)

def read_all_values(src, pos):
    length = len(src)
    while (sp := read_values(src, pos)):
        s, pos = sp
        yield s
        if pos >= length or src[pos] != ",": break
        pos += 1

if test:
    src = r"(1,2,3),(1,'a,b','c\'d')"
    print("read_all_values", (src, 0), "->", list(read_all_values(src, 0)))

Résultat d'exécution

read_all_values ("(1,2,3),(1,'a,b','c\\'d')", 0) -> [['1', '2', '3'], ['1', 'a,b', "c'd"]]

read_sql

Trouvez la ligne commençant par ʻINSERT INTO de chaque ligne dans SQL et traitez-la avec read_all_values`.

sql2tsv.py (suite)

def read_sql(stream):
    while (line := stream.readline()):
        if line.startswith("INSERT INTO "):
            p = line.find("VALUES (")
            if p >= 0: yield from read_all_values(line, p + 7)

if test:
    import io
    src = r"""
INSERT INTO `table` VALUES (1,2,3),(4,5,6);
INSERT INTO `table` VALUES (1,'a,b','c\'d');
""".strip()
    print("read_sql", (src,))
    print("->", list(read_sql(io.StringIO(src))))

Résultat d'exécution

read_sql ("INSERT INTO `table` VALUES (1,2,3),(4,5,6);\nINSERT INTO `table` VALUES (1,'a,b','c\\'d');",)
-> [['1', '2', '3'], ['4', '5', '6'], ['1', 'a,b', "c'd"]]

Commander

J'ai implémenté toutes les fonctions nécessaires. Désactivez le test.

sql2tsv.py (changer)

test = False

Lit le fichier spécifié et l'écrit dans le fichier spécifié.

ʻOpenspécifie ʻerrors = "replace", en supposant que la chaîne d'octets UTF-8 est coupée au milieu d'un caractère. Cela remplacera le caractère anormal par. Si ʻerrors` n'est pas spécifié, une erreur se produira.

[Référence] (Windows) Causes et solutions de contournement pour UnicodeEncodeError dans Python 3 --Qiita

sql2tsv.py (suite)

if __name__ == "__main__":
    import sys
    try:
        sql, tsv = sys.argv[-2], sys.argv[-1]
        if not (sql.endswith(".sql") and tsv.endswith(".tsv")):
            raise Exception
    except:
        print("usage: %s sql tsv" % sys.argv[0])
        exit(1)
    with open(sql, "r", encoding="utf-8", errors="replace") as fr:
        with open(tsv, "w", encoding="utf-8") as fw:
            for values in read_sql(fr):
                fw.write("\t".join(values))
                fw.write("\n")

Utilisez-le comme suit.

python sql2tsv.py input.sql output.tsv

J'ai mis tout le script ci-dessous.

• [py] mysqldump (SQL) to TSV converter

la mesure

Il mesure la vitesse à laquelle il peut être traité avec un gros fichier.

Version japonaise du Wiktionnaire

Comme avec Wikipédia, les données de vidage sont ouvertes au public.

• https://dumps.wikimedia.org/jawiktionary/

Les fichiers suivants seront utilisés à partir de la version du 1er mai 2020 disponible au moment de la rédaction de l'article.

• jawiktionary-20200501-categorylinks.sql.gz 10.7 MB

L'extraction du fichier sera d'environ 90 Mo.

gunzip -k jawiktionary-20200501-categorylinks.sql.gz

• -K est une option pour conserver le fichier avant l'extraction.

La conversion du SQL étendu avec ce script produira un TSV d'environ 79 Mo.

$ time python sql2tsv.py jawiktionary-20200501-categorylinks.sql jawiktionary-20200501-categorylinks.tsv

real    0m19.921s
user    0m19.375s
sys     0m0.516s

Le nombre de lignes est d'environ 950 000.

$ wc -l jawiktionary-20200501-categorylinks.tsv
951077 jawiktionary-20200501-categorylinks.tsv

Version anglaise du Wiktionnaire

Essayez ensuite la version anglaise.

• https://dumps.wikimedia.org/enwiktionary/

Utilisez les fichiers suivants. Une fois déplié, il gonfle à environ 3 Go.

• enwiktionary-20200501-categorylinks.sql.gz 344.6 MB

Convertissez ceci. Le fichier de sortie fait environ 2,6 Go.

$ time python sql2tsv.py enwiktionary-20200501-categorylinks.sql enwiktionary-20200501-categorylinks.tsv

real    15m58.965s
user    15m39.063s
sys     0m12.578s

Le nombre de lignes peut atteindre 28 millions.

$ wc -l enwiktionary-20200501-categorylinks.tsv
28021874 enwiktionary-20200501-categorylinks.tsv

F#

Je l'ai porté sur F # à titre d'essai.

• [F#] mysqldump (SQL) to TSV converter

Le traitement du même fichier était beaucoup plus rapide.

Lire le fichier compressé

Le .NET Framework dispose d'un GZipStream qui vous permet de lire directement un fichier gz compressé.

sql2tsv.fsx (modifier)

open System.IO.Compression
let args = Environment.GetCommandLineArgs()
let sqlgz, tsv =
    if args.Length < 2 then ("", "") else
    let len = args.Length
    (args.[len - 2], args.[len - 1])
if not (sqlgz.EndsWith ".sql.gz") || Path.GetExtension tsv <> ".tsv" then
    printfn "usage: sql2tsv sql.gz tsv"
    exit 1
do
    use fs = new FileStream(sqlgz, FileMode.Open)
    use gs = new GZipStream(fs, CompressionMode.Decompress)
    use sr = new StreamReader(gs)
    use sw = new StreamWriter(tsv)
    sw.NewLine <- "\n"
    for values in readSql sr do
        sw.WriteLine(String.concat "\t" values)

Les résultats des mesures sont résumés ci-dessous.

Résumé

Organisez les résultats de mesure.

Le traitement direct des fichiers compressés est un peu plus lent, mais cela économise de l'espace disque, ce qui est un avantage.