Création d'une table dans Oracle NoSQL Database Cloud Service
Etapes à suivre pour créer une table dans Oracle NoSQL Database Cloud Service et l'alimenter avec des données.
Création et remplissage d'une table
Vous pouvez créer une table dans Oracle NoSQL Database Cloud Service et l'alimenter avec des données. Pour ce faire, vous devez pouvoir vous connecter à NoSQL Cloud Service à l'aide de vos propres informations d'identification ou en tant que principal d'instance autorisé à partir d'une instance Oracle Cloud Compute. Si vous ne connaissez pas NoSQL Cloud Service, le tutoriel et l'atelier de 30 minutes peuvent vous aider à démarrer. Ce tutoriel présente un exemple de base qui explique comment créer et remplir une table simple dans NoSQL Cloud Service à l'aide de vos propres informations d'identification.
Exemple d'application : Créer et alimenter une table complexe
Le programme présenté ici explique comment créer une table dans NoSQL Cloud Service et comment y remplir des données complexes. Pour utiliser l'application présentée dans cette section, copiez d'abord le programme Java CreateLoadComplexTable.java dans le répertoire examples/nosql/cloud/table de votre environnement. Vous pouvez ensuite compiler et exécuter l'application.
package nosql.cloud.table;
import java.io.File;
import java.math.BigDecimal;
import java.security.SecureRandom;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import oracle.nosql.driver.NoSQLHandle;
import oracle.nosql.driver.NoSQLHandleConfig;
import oracle.nosql.driver.NoSQLHandleFactory;
import oracle.nosql.driver.Region;
import oracle.nosql.driver.iam.SignatureProvider;
import oracle.nosql.driver.ops.DeleteRequest;
import oracle.nosql.driver.ops.DeleteResult;
import oracle.nosql.driver.ops.GetRequest;
import oracle.nosql.driver.ops.GetResult;
import oracle.nosql.driver.ops.PutRequest;
import oracle.nosql.driver.ops.PutResult;
import oracle.nosql.driver.ops.QueryRequest;
import oracle.nosql.driver.ops.QueryResult;
import oracle.nosql.driver.ops.TableLimits;
import oracle.nosql.driver.ops.TableRequest;
import oracle.nosql.driver.ops.TableResult;
import oracle.nosql.driver.util.TimestampUtil;
import oracle.nosql.driver.values.ArrayValue;
import oracle.nosql.driver.values.LongValue;
import oracle.nosql.driver.values.MapValue;
import oracle.nosql.driver.values.StringValue;
public final class CreateLoadComplexTable {
private static final SecureRandom generator = new SecureRandom();
private final NoSQLHandle ociNoSqlHndl;
private long nOps = 10; /* Default number of rows. */
private long nRowsAdded;
private boolean deleteExisting = false;
private static String compartment = "";
private static String tableName = "";
public static void main(final String[] args) {
try {
final CreateLoadComplexTable loadData =
new CreateLoadComplexTable(args);
loadData.run();
System.exit(0);
} catch (Throwable e) {
e.printStackTrace();
System.out.println("Failed to create and populate the " +
"requested table [name = " + tableName +
"]");
System.exit(1);
}
}
private CreateLoadComplexTable(final String[] argv) {
String tenantOcid = "";
String userOcid = "";
String fingerprint = "";
String privateKeyFilename = "";
String passStr = null;
File privateKeyFile = null;
char[] passPhrase = null;
final int nArgs = argv.length;
int argc = 0;
if (nArgs == 0) {
usage(null);
}
while (argc < nArgs) {
final String thisArg = argv[argc++];
if ("-tenant".equals(thisArg)) {
if (argc < nArgs) {
tenantOcid = argv[argc++];
} else {
usage("-tenant argument requires an argument");
}
} else if ("-user".equals(thisArg)) {
if (argc < nArgs) {
userOcid = argv[argc++];
} else {
usage("-user requires an argument");
}
} else if ("-fp".equals(thisArg)) {
if (argc < nArgs) {
fingerprint = argv[argc++];
} else {
usage("-fp requires an argument");
}
} else if ("-pem".equals(thisArg)) {
if (argc < nArgs) {
privateKeyFilename = argv[argc++];
privateKeyFile = new File(privateKeyFilename);
} else {
usage("-pem requires an argument");
}
} else if ("-compartment".equals(thisArg)) {
if (argc < nArgs) {
compartment = argv[argc++];
} else {
usage("-compartment requires an argument");
}
} else if ("-table".equals(thisArg)) {
if (argc < nArgs) {
tableName = argv[argc++];
} else {
usage("-table requires an argument");
}
} else if ("-n".equals(thisArg)) {
if (argc < nArgs) {
nOps = Long.parseLong(argv[argc++]);
} else {
usage("-n requires an argument");
}
} else if ("-phrase".equals(thisArg)) {
passStr = argv[argc++];
passPhrase = passStr.toCharArray();
} else if ("-delete".equals(thisArg)) {
deleteExisting = true;
} else {
usage("Unknown argument: " + thisArg);
}
}
nRowsAdded = nOps;
System.out.println("COMPARTMENT: " + compartment);
System.out.println("TABLE: " + tableName);
final SignatureProvider auth =
new SignatureProvider(tenantOcid, userOcid, fingerprint,
privateKeyFile, passPhrase);
final NoSQLHandleConfig config =
new NoSQLHandleConfig(Region.US_ASHBURN_1, auth);
ociNoSqlHndl = NoSQLHandleFactory.createNoSQLHandle(config);
createTable();
}
private void usage(final String message) {
if (message != null) {
System.out.println("\n" + message + "\n");
}
System.out.println("usage: " + getClass().getName());
System.out.println
("\t-tenant <tenant ocid>\n" +
"\t-user <user ocid>\n" +
"\t-fp <fingerprint>\n" +
"\t-pem <private key file>\n" +
"\t-compartment <compartment name>\n" +
"\t-table <table name>\n" +
"\t-n <total records to create>\n" +
"\t[-phrase <[pass phrase>]\n" +
"\t-delete (default: false) [delete all “ +
“pre-existing data]\n");
System.exit(1);
}
private void run() {
if (deleteExisting) {
deleteExistingData();
}
doLoad();
}
private void createTable() {
final int readUnits = 10;
final int writeUnits = 10;
final int storageGb = 1;
final int ttlDays = 1;
/* Wait no more than 2 minutes for table create. */
final int waitMs = 2 * 60 * 1000;
/* Check for table existence every 2 seconds. */
final int delayMs = 2 * 1000;
/* Table creation statement. */
final String statement =
"CREATE TABLE IF NOT EXISTS " + tableName +
" (" +
"ID INTEGER," +
"AINT INTEGER," +
"ALON LONG," +
"ADOU DOUBLE," +
"ANUM NUMBER," +
"AUUID STRING," +
"ATIM_P0 TIMESTAMP(0)," +
"ATIM_P3 TIMESTAMP(3)," +
"ATIM_P6 TIMESTAMP(6)," +
"ATIM_P9 TIMESTAMP(9)," +
"AENU ENUM(S,M,L,XL,XXL,XXXL)," +
"ABOO BOOLEAN," +
"ABIN BINARY," +
"AFBIN BINARY(16)," +
"ARRY ARRAY (INTEGER)," +
"AMAP MAP (DOUBLE)," +
"AREC RECORD(" +
"BLON LONG," +
"BTIM_P6 TIMESTAMP(6)," +
"BNUM NUMBER," +
"BSTR STRING," +
"BRRY ARRAY(DOUBLE))," +
"AJSON JSON," +
"PRIMARY KEY (SHARD(AINT), ALON, ADOU, ID)" +
")" +
" USING TTL " + ttlDays + " days";
System.out.println(statement);
final TableRequest tblRqst = new TableRequest();
tblRqst.setCompartment(compartment).setStatement(statement);
final TableLimits tblLimits =
new TableLimits(readUnits, writeUnits, storageGb);
tblRqst.setTableLimits(tblLimits);
final TableResult tblResult =
ociNoSqlHndl.tableRequest(tblRqst);
tblResult.waitForCompletion(ociNoSqlHndl, waitMs, delayMs);
if (tblResult.getTableState() != TableResult.State.ACTIVE) {
final String msg =
"TIMEOUT: Failed to create table in OCI NoSQL “ +
“[table=" + tableName + "]";
throw new RuntimeException(msg);
}
}
private void doLoad() {
final List<MapValue> rows = generateData(nOps);
for (MapValue row : rows) {
addRow(row);
}
displayRow();
final long nRowsTotal = nRowsInTable();
if (nOps > nRowsAdded) {
System.out.println(
nOps + " records requested, " +
nRowsAdded + " unique records actually added " +
"[" + (nOps - nRowsAdded) + " duplicates], " +
nRowsTotal + " records total in table");
} else {
System.out.println(
nOps + " records requested, " +
nRowsAdded + " unique records added, " +
nRowsTotal + " records total in table");
}
}
private void addRow(final MapValue row) {
final PutRequest putRqst = new PutRequest();
putRqst.setCompartment(compartment).setTableName(tableName);
putRqst.setValue(row);
final PutResult putRslt = ociNoSqlHndl.put(putRqst);
if (putRslt.getVersion() == null) {
final String msg =
"PUT: Failed to insert row [table=" + tableName +
", row = " + row + "]";
}
}
/* Retrieves and deletes each row from the table. */
private void deleteExistingData() {
final String selectAll = "SELECT * FROM " + tableName;
final QueryRequest queryRqst = new QueryRequest();
queryRqst.setCompartment(compartment).setStatement(selectAll);
long cnt = 0;
do {
QueryResult queryRslt = ociNoSqlHndl.query(queryRqst);
final List<MapValue> rowMap = queryRslt.getResults();
for (MapValue row : rowMap) {
final DeleteRequest delRqst = new DeleteRequest();
delRqst.setCompartment(compartment)
.setTableName(tableName);
delRqst.setKey(row);
final DeleteResult delRslt =
ociNoSqlHndl.delete(delRqst);
if (delRslt.getSuccess()) {
cnt++;
}
}
} while (!queryRqst.isDone());
System.out.println(cnt + " records deleted");
}
/* Counts the number of rows in the table. */
private long nRowsInTable() {
final String selectAll = "SELECT * FROM " + tableName;
final QueryRequest queryRqst = new QueryRequest();
queryRqst.setCompartment(compartment).setStatement(selectAll);
long cnt = 0;
do {
QueryResult queryRslt = ociNoSqlHndl.query(queryRqst);
final List<MapValue> rowMap = queryRslt.getResults();
for (MapValue row : rowMap) {
cnt++;
}
} while (!queryRqst.isDone());
return cnt;
}
/* Convenience method for displaying output when debugging. */
private void displayRow() {
final String selectAll = "SELECT * FROM " + tableName;
final QueryRequest queryRqst = new QueryRequest();
queryRqst.setCompartment(compartment).setStatement(selectAll);
do {
QueryResult queryRslt = ociNoSqlHndl.query(queryRqst);
final List<MapValue> rowMap = queryRslt.getResults();
for (MapValue row : rowMap) {
System.out.println(row);
}
} while (!queryRqst.isDone());
}
/* Generates randomized data with which to populate the table. */
private List<MapValue> generateData(final long count) {
List<MapValue> rows = new ArrayList<>();
final BigDecimal[] numberArray = {
new BigDecimal("3E+8"),
new BigDecimal("-1.7976931348623157E+2"),
new BigDecimal("12345.76455"),
new BigDecimal("12345620.789"),
new BigDecimal("1234562078912345678988765446777475657"),
new BigDecimal("1.7976931348623157E+305"),
new BigDecimal("-1.7976931348623157E+304")
};
final Timestamp[] timeArray_p0 = {
TimestampUtil.parseString("2010-05-05T10:45:00"),
TimestampUtil.parseString("2011-05-05T10:45:01"),
Timestamp.from(Instant.parse("2021-07-15T11:31:21Z"))
};
final Timestamp[] timeArray_p3 = {
TimestampUtil.parseString("2011-05-05T10:45:01.123"),
Timestamp.from(
Instant.parse("2021-07-15T11:31:47.549Z")),
Timestamp.from(
Instant.parse("2021-07-15T11:32:12.836Z"))
};
final Timestamp[] timeArray_p6 = {
TimestampUtil.parseString(
"2014-05-05T10:45:01.789456Z"),
TimestampUtil.parseString(
"2013-08-20T12:34:56.123456Z"),
Timestamp.from(Instant.parse(
"2021-07-15T11:31:47.549213Z")),
Timestamp.from(Instant.parse(
"2021-07-15T11:32:12.567836Z"))
};
final Timestamp[] timeArray_p9 = {
Timestamp.from(Instant.parse(
"2021-07-15T12:46:35.574639954Z")),
Timestamp.from(Instant.parse(
"2021-07-15T12:47:32.883922660Z")),
Timestamp.from(Instant.parse(
"2021-07-15T12:48:11.321131987Z"))
};
final String[] enumArray =
{"S", "M", "L", "XL", "XXL", "XXXL"};
for(int i = 1; i <= count; ++i) {
byte[] byteArray = new byte[16];
generator.nextBytes(byteArray);
MapValue row = new MapValue(true,16);
row.put("ID", i);
row.put("AINT", generator.nextInt());
row.put("ALON", generator.nextLong());
row.put("ADOU", generator.nextDouble());
row.put("ANUM",
numberArray[generator.nextInt(
numberArray.length)]);
row.put("AUUID", UUID.randomUUID().toString());
/* TIMESTAMP */
row.put("ATIM_P0",
timeArray_p0[generator.nextInt(
timeArray_p0.length)]);
row.put("ATIM_P3",
timeArray_p3[generator.nextInt(
timeArray_p3.length)]);
row.put("ATIM_P6",
timeArray_p6[generator.nextInt(
timeArray_p6.length)]);
row.put("ATIM_P9",
timeArray_p9[generator.nextInt(
timeArray_p9.length)]);
/* ENUM */
row.put("AENU", enumArray[i % enumArray.length]);
/* BOOLEAN */
row.put("ABOO", generator.nextBoolean());
/* BINARY & FIXED_BINARY stored as strings */
row.put("ABIN", byteArray);
row.put("AFBIN", byteArray);
/* ARRAY of INTEGER */
ArrayValue integerArr = new ArrayValue();
for (int j = 0; j < 3; ++j) {
integerArr.add(generator.nextInt());
}
row.put("ARRY", integerArr);
/* MAP of DOUBLE */
MapValue map = new MapValue(true,3);
map.put("d1", generator.nextDouble());
map.put("d2", generator.nextDouble());
row.put("AMAP", map);
/*
* RECORD of: LONG, TIMESTAMP, NUMBER,
* STRING, ARRAY of DOUBLE
*/
MapValue record = new MapValue(true,5);
/* LONG element */
record.put("BLON", generator.nextLong());
/* TIMESTAMP element */
record.put("BTIM_P6",
timeArray_p6[generator.nextInt(
timeArray_p6.length)]);
/* NUMBER element */
record.put("BNUM",
numberArray[generator.nextInt(
numberArray.length)]);
/* STRING element */
record.put("BSTR", Double.toString(
generator.nextDouble()));
/* ARRAY of DOUBLE element */
ArrayValue doubleArr = new ArrayValue();
for (int j = 0; j < 3; ++j) {
doubleArr.add(generator.nextDouble());
}
record.put("BRRY", doubleArr);
row.put("AREC", record);
/* JSON */
MapValue json = new MapValue(true,5);
json.put("id", i);
json.put("name", "name_" + i);
json.put("age", i + 10);
row.put("AJSON", json);
rows.add(row);
}
return rows;
}
}
Compilation et exécution de l'application
Compiler l'application
Pour compiler l'application
CreateLoadComplexTable.java, commencez par remplacer le répertoire par le parent du répertoire des exemples. En supposant que vous avez installé le kit SDK Oracle NoSQL pour Java sous ce répertoire, saisissez la commande suivante :javac -classpath oracle-nosql-java-sdk/lib/nosqldriver.jar:examples
examples/nosql/cloud/table/CreateLoadComplexTable.javaExécution de l'application
Une fois la compilation terminée, vous pouvez exécuter l'application en saisissant la commande suivante :
java -classpath oracle-nosql-java-sdk/lib/nosqldriver.jar:examples nosql.cloud.table.CreateLoadComplexTable
-tenant <ocid-of-your-tennancy> -user <your-user-ocid> -fp <your-finger-print> -pem <path-to-your-oci-private-key-file>
-compartment <compartment-ocid-for-the-table> -table tableName1 [-n 11] [-delete]Dans l'exemple ci-dessus, tous les arguments liés à vos informations d'identification utilisateur sont requis (-tenant, -fp, -pem, and -compartment), tout comme l'argument -table, qui indique le nom de la table à créer et à charger avec les données. Les arguments restants (-n and -delete) sont facultatifs.
Si l'argument -n est indiqué, la valeur indiquée représente le nombre de nouvelles lignes à générer et à écrire dans la table. Si l'argument n'est pas spécifié, 10 lignes sont écrites dans la table par défaut.
Si l'argument -delete est spécifié, toutes les lignes existantes écrites dans la table par les exécutions précédentes de l'application sont d'abord supprimées de la table avant d'ajouter de nouvelles lignes.
Une fois l'exécution de l'application terminée, vous pouvez vérifier que la table existe et qu'elle contient des données en vous connectant à la console Oracle Cloud, en accédant à la section Tables du service Oracle NoSQL Database et en interrogeant la table portant le nom indiqué pour l'argument -table.
Création d'une table dans Oracle NoSQL Database Cloud Service
Copyright ©2022,
Oracle et/ou ses affiliés.