在 Oracle NoSQL Database Cloud Service 中创建表
在 Oracle NoSQL Database Cloud Service 中创建表并使用数据填充表的步骤。
创建并填充表
可以在 Oracle NoSQL Database Cloud Service 中创建表并使用数据填充表。为此，您必须能够使用自己的身份证明或通过 Oracle Cloud 计算实例的授权实例主用户身份连接到 NoSQL Cloud Service。如果您是 NoSQL Cloud Service 的新手， 30 分钟的教程和练习可以帮助您开始使用。本教程提供了一个基本示例，说明如何使用您自己的身份证明在 NoSQL Cloud Service 中创建和填充简单表。
示例应用程序：创建和填充复杂表
此处显示的程序显示了如何在 NoSQL Cloud Service 中创建表并使用复杂数据填充该表。要使用本部分提供的应用程序，首先，将 Java 程序 CreateLoadComplexTable.java 复制到环境中的目录 example/nosql/cloud/table。然后，您可以编译并执行应用程序。
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;
}
}编译和运行应用程序
编译应用产品
要编译 CreateLoadComplexTable.java 应用程序，请首先将目录更改为示例目录的父目录。然后，假定在该目录下安装了 Oracle NoSQL SDK for Java ，请键入以下命令：
javac -classpath oracle-nosql-java-sdk/lib/nosqldriver.jar:examples
examples/nosql/cloud/table/CreateLoadComplexTable.java运行应用程序
成功编译后，可以通过键入以下命令来执行应用程序：
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]在上面的示例中，需要与您的用户凭证相关的所有参数 (-tenant, -fp, -pem, and -compartment)，参数 -table 也是如此，该参数指定要使用数据创建和加载的表的名称。其余参数 (-n and -delete) 是可选的。
如果指定了 -n 参数，则指定的值表示要生成并写入表的新行数。如果未指定该参数，则默认情况下会向表中写入 10 行。
如果指定了 -delete 参数，则在添加任何新行之前，将首先从表中删除以前执行的应用程序写入到表的所有现有行。
应用程序执行完毕后，可以通过登录 Oracle Cloud 控制台、导航到 Oracle NoSQL Database 服务的表部分并查询具有您为 -table 参数指定的名称的表来验证表是否存在并填充了数据。