Search code examples
sqlprimary-keypentahokettlespoon

Pentaho (spoon): generate primary key for many rows in SQL

I have a case: to transfer data from access database to SQL. I have faced with a problem how to create a lot of new primary keys in SQL.

When I had been transfering previos tables, I used Generator Random Values to generate Random String. The new key I cut to 5 figures.

enter image description here

But now I have a lot of row 900 000 and about 200 new keys are consisted.

I could not use Add sequence because primary key has a length of 5 figures (char(5)).

I think I can use java to create a key and find this script, but I do not understant how made work it.

enter image description here

enter image description here

import java.security.SecureRandom;
import java.util.Locale;
import java.util.Objects;
import java.util.Random;
public class RandomString {
    public String nextString() {
        for (int idx = 0; idx < buf.length; ++idx)
            buf[idx] = symbols[random.nextInt(symbols.length)];
        return new String(buf);
    }
    public static final String upper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    public static final String lower = upper.toLowerCase(Locale.ROOT);
    public static final String digits = "0123456789";
    public static final String alphanum = upper + lower + digits;
    private final Random random;
    private final char[] symbols;
    private final char[] buf;
    public RandomString(int length, Random random, String symbols) {
        if (length < 1) throw new IllegalArgumentException();
        if (symbols.length() < 2) throw new IllegalArgumentException();
        this.random = Objects.requireNonNull(random);
        this.symbols = symbols.toCharArray();
        this.buf = new char[length];
    }

enter image description here

My database does not have any sequence to use Add sequence and Use DB to get sequence, but has a function to generate keys. How can I use this function? Or not?

This is a statistic of lose data. May I create a cycle to re-generate key in such situation?

enter image description here

Solution

  • Instead of create random keys, use a sequence the convert the sequence in number with a high radix (for example with the javascript id_as_char = id_as_int.toString(36)).

    Of course, you have other ways !

    enter image description here