Object Storage Service：Kueri objek

Cara kerja

Saat menggunakan GetObject, aplikasi Anda mengunduh seluruh objek sebelum melakukan penyaringan. SelectObject memindahkan proses penyaringan ke dalam OSS itu sendiri: Anda mengirimkan kondisi dan proyeksi SQL, lalu OSS hanya mengembalikan baris-baris yang sesuai.

Alur kueri:

1. Kirim permintaan SelectObject dengan pernyataan SQL SELECT ... FROM ... WHERE dan format output.

2. OSS memindai objek dan menerapkan kondisi serta proyeksi Anda di sisi server.

3. OSS mengalirkan hanya catatan-catatan yang cocok kembali ke klien Anda.

4. Untuk objek besar, panggil CreateSelectObjectMeta terlebih dahulu untuk mendapatkan jumlah total split, lalu jalankan kueri paralel menggunakan split-range.

Jenis objek yang didukung

Batasan

Penagihan

Biaya SelectObject didasarkan pada jumlah data yang dipindai:

• Objek Standar: biaya pemindaian data.

• Objek IA, Archive, Cold Archive, dan Deep Cold Archive: biaya pemindaian data ditambah biaya pengambilan data.

Untuk detail harga, lihat Biaya pemrosesan data.

Sintaks SQL yang didukung

SELECT s.a FROM ossobject s WHERE CAST(s.a AS decimal) > 123456789.12345

SELECT s.a FROM ossobject s WHERE CAST(s.a AS decimal) > 123456789.12345

Contoh SQL

{
  "contacts": [
    {
      "firstName": "John",
      "lastName": "Smith",
      "age": 27,
      "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021-3100"
      },
      "phoneNumbers": [
        { "type": "home", "number": "212 555-1234" },
        { "type": "office", "number": "646 555-4567" },
        { "type": "mobile", "number": "123 456-7890" }
      ],
      "children": [],
      "spouse": null
    }
  ]
}

Kueri objek menggunakan Konsol OSS

1. Masuk ke Konsol OSS.

2. Pada panel navigasi kiri, klik Buckets. Temukan dan klik bucket target.

3. Pada pohon navigasi kiri, pilih Object Management > Objects.

4. Pada kolom Actions untuk objek target, pilih > Select Content.

5. Pada panel Select Content, konfigurasikan parameter berikut:

   Parameter	Description
   Object Type	CSV atau JSON
   Delimiter	Hanya untuk CSV. , atau Custom
   Title Row	Hanya untuk CSV. Apakah baris pertama berisi header kolom
   JSON Display Mode	Hanya untuk JSON. JSON_LINES atau JSON_DOCUMENT
   Compression Format	Hanya GZIP

6. Klik Preview untuk melihat pratinjau data yang dipilih.

   Penting

   Pratinjau objek Standard dikenai biaya pemindaian data. Pratinjau objek IA, Archive, Cold Archive, atau Deep Cold Archive dikenai biaya pemindaian data ditambah biaya pengambilan data. Untuk detailnya, lihat Biaya pemrosesan data.

7. Klik Next, lalu masukkan dan jalankan pernyataan SQL. Contoh: Objek CSV bernama People memiliki tiga kolom: Name, Company, dan Age. Indeks kolomnya adalah _1 (Name), _2 (Company), dan _3 (Age).

   • Kueri orang berusia di atas 50 yang namanya diawali Lora:

     SELECT * FROM ossobject WHERE _1 LIKE 'Lora*' AND _3 > 50

   • Ambil jumlah baris, usia maksimum, dan usia minimum:

     SELECT COUNT(*), MAX(CAST(_3 AS int)), MIN(CAST(_3 AS int)) FROM oss_object

8. Lihat hasilnya. Klik Download untuk menyimpan output ke mesin lokal Anda.

Kueri objek menggunakan OSS SDK

Hanya OSS SDK for Java dan OSS SDK for Python yang mendukung SelectObject.

import com.aliyun.oss.model.*;
import com.aliyun.oss.OSS;
import com.aliyun.oss.common.auth.*;
import com.aliyun.oss.OSSClientBuilder;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.InputStreamReader;

public class SelectObjectSample {
    // Ganti dengan endpoint wilayah bucket Anda.
    // Contoh: https://oss-cn-hangzhou.aliyuncs.com
    private static String endpoint = "https://oss-cn-hangzhou.aliyuncs.com";
    private static String bucketName = "examplebucket";

    public static void main(String[] args) throws Exception {
        // Muat kredensial dari variabel lingkungan OSS_ACCESS_KEY_ID dan OSS_ACCESS_KEY_SECRET.
        EnvironmentVariableCredentialsProvider credentialsProvider =
            CredentialsProviderFactory.newEnvironmentVariableCredentialsProvider();
        String region = "cn-hangzhou";

        ClientBuilderConfiguration clientBuilderConfiguration = new ClientBuilderConfiguration();
        clientBuilderConfiguration.setSignatureVersion(SignVersion.V4);
        OSS ossClient = OSSClientBuilder.create()
            .endpoint(endpoint)
            .credentialsProvider(credentialsProvider)
            .clientConfiguration(clientBuilderConfiguration)
            .region(region)
            .build();

        // Kueri objek CSV.
        selectCsvSample("test.csv", ossClient);
        // Kueri objek JSON LINES.
        selectJsonSample("test.json", ossClient);

        ossClient.shutdown();
    }

    private static void selectCsvSample(String key, OSS ossClient) throws Exception {
        // Unggah data CSV contoh dengan baris header.
        String content = "name,school,company,age\r\n" +
                "Lora Francis,School A,Staples Inc,27\r\n" +
                "Eleanor Little,School B,\"Conectiv, Inc\",43\r\n" +
                "Rosie Hughes,School C,Western Gas Resources Inc,44\r\n" +
                "Lawrence Ross,School D,MetLife Inc.,24";
        ossClient.putObject(bucketName, key, new ByteArrayInputStream(content.getBytes()));

        // Buat metadata split untuk objek (disarankan sebelum kueri multipart).
        SelectObjectMetadata selectObjectMetadata = ossClient.createSelectObjectMetadata(
            new CreateSelectObjectMetadataRequest(bucketName, key)
                .withInputSerialization(
                    new InputSerialization().withCsvInputFormat(
                        new CSVFormat()
                            .withHeaderInfo(CSVFormat.Header.Use)
                            .withRecordDelimiter("\r\n"))));
        System.out.println("Total lines: " + selectObjectMetadata.getCsvObjectMetadata().getTotalLines());
        System.out.println("Total splits: " + selectObjectMetadata.getCsvObjectMetadata().getSplits());

        // Kueri baris di mana kolom ke-4 (usia) lebih besar dari 40.
        SelectObjectRequest selectObjectRequest =
            new SelectObjectRequest(bucketName, key)
                .withInputSerialization(
                    new InputSerialization().withCsvInputFormat(
                        new CSVFormat()
                            .withHeaderInfo(CSVFormat.Header.Use)
                            .withRecordDelimiter("\r\n")))
                .withOutputSerialization(new OutputSerialization().withCsvOutputFormat(new CSVFormat()));
        selectObjectRequest.setExpression("SELECT * FROM ossobject WHERE _4 > 40");

        OSSObject ossObject = ossClient.selectObject(selectObjectRequest);
        BufferedReader reader = new BufferedReader(new InputStreamReader(ossObject.getObjectContent()));
        String line;
        while ((line = reader.readLine()) != null) {
            System.out.println(line);
        }
        reader.close();
        ossClient.deleteObject(bucketName, key);
    }

    private static void selectJsonSample(String key, OSS ossClient) throws Exception {
        // Unggah data JSON LINES contoh (satu objek JSON per baris).
        final String content = "{\"name\":\"Lora Francis\",\"age\":27,\"company\":\"Staples Inc\"}\n" +
                "{\"name\":\"Eleanor Little\",\"age\":43,\"company\":\"Conectiv, Inc\"}\n" +
                "{\"name\":\"Rosie Hughes\",\"age\":44,\"company\":\"Western Gas Resources Inc\"}\n" +
                "{\"name\":\"Lawrence Ross\",\"age\":24,\"company\":\"MetLife Inc.\"}";
        ossClient.putObject(bucketName, key, new ByteArrayInputStream(content.getBytes()));

        // Kueri catatan di mana usia > 40.
        SelectObjectRequest selectObjectRequest =
            new SelectObjectRequest(bucketName, key)
                .withInputSerialization(new InputSerialization()
                    .withCompressionType(CompressionType.NONE)
                    .withJsonInputFormat(new JsonFormat().withJsonType(JsonType.LINES)))
                .withOutputSerialization(new OutputSerialization()
                    .withCrcEnabled(true)
                    .withJsonOutputFormat(new JsonFormat()))
                .withExpression("SELECT * FROM ossobject AS s WHERE s.age > 40");

        OSSObject ossObject = ossClient.selectObject(selectObjectRequest);
        BufferedReader reader = new BufferedReader(new InputStreamReader(ossObject.getObjectContent()));
        String line;
        while ((line = reader.readLine()) != null) {
            System.out.println(line);
        }
        reader.close();
        ossClient.deleteObject(bucketName, key);
    }
}

import oss2
from oss2.credentials import EnvironmentVariableCredentialsProvider

def select_callback(consumed_bytes, total_bytes=None):
    print(f"Consumed bytes: {consumed_bytes}")

# Muat kredensial dari variabel lingkungan.
auth = oss2.ProviderAuthV4(EnvironmentVariableCredentialsProvider())

endpoint = "https://oss-cn-hangzhou.aliyuncs.com"  # Ganti dengan endpoint bucket Anda.
region = "cn-hangzhou"                              # Ganti dengan wilayah bucket Anda.
bucket = oss2.Bucket(auth, endpoint, "examplebucket", region=region)

# --- Kueri CSV ---

key = "python_select.csv"
content = "Tom Hanks,USA,45\r\n" * 1024
bucket.put_object(key, content)

# Buat metadata split.
csv_meta_params = {"RecordDelimiter": "\r\n"}
csv_header = bucket.create_select_object_meta(key, csv_meta_params)
print(f"Total rows: {csv_header.rows}, total splits: {csv_header.splits}")

# Kueri baris di mana kolom 3 > 44.
select_csv_params = {
    "CsvHeaderInfo": "None",
    "RecordDelimiter": "\r\n",
    "LineRange": (500, 1000),
}
result = bucket.select_object(
    key, "SELECT * FROM ossobject WHERE _3 > 44", select_callback, select_csv_params
)
print(result.read())

# Simpan hasil ke file lokal.
bucket.select_object_to_file(
    key, "python_select.csv", "SELECT * FROM ossobject WHERE _3 > 44",
    select_callback, select_csv_params
)
bucket.delete_object(key)

# --- Kueri JSON DOCUMENT ---

key = "python_select.json"
content = '{"contacts":[{"key1":1,"key2":"hello world1"},{"key1":2,"key2":"hello world2"}]}'
bucket.put_object(key, content)

select_json_params = {"Json_Type": "DOCUMENT"}
result = bucket.select_object(
    key,
    "SELECT s.key2 FROM ossobject.contacts[*] s WHERE s.key1 = 1",
    None,
    select_json_params,
)
print(result.read())
bucket.delete_object(key)

# --- Kueri JSON LINES ---

key = "python_select_lines.json"
content = '{"key1":1,"key2":"hello world1"}\n{"key1":2,"key2":"hello world2"}'
bucket.put_object(key, content)

select_json_params = {"Json_Type": "LINES"}
json_header = bucket.create_select_object_meta(key, select_json_params)
print(f"Total rows: {json_header.rows}, total splits: {json_header.splits}")

result = bucket.select_object(
    key, "SELECT s.key2 FROM ossobject s WHERE s.key1 = 1", None, select_json_params
)
print(result.read())
bucket.delete_object(key)

Kueri objek menggunakan API OSS

Untuk memanggil SelectObject langsung melalui RESTful API, sertakan perhitungan signature dalam kode Anda. Untuk detailnya, lihat SelectObject.

Jalankan kueri paralel pada objek besar

Untuk objek besar, bagi kueri ke beberapa permintaan konkuren menggunakan split-range.

Kapan menggunakan split vs. byte range:

Langkah-langkah untuk kueri paralel berbasis split:

1. Panggil CreateSelectObjectMeta untuk mendapatkan jumlah total split. Panggil secara asinkron sebelum kueri pertama untuk menghindari overhead pemindaian tambahan.

2. Pilih tingkat konkurensi n berdasarkan sumber daya klien yang tersedia.

3. Bagi jumlah total split dengan n untuk mendapatkan jumlah split per permintaan.

4. Kirimkan n permintaan SelectObject konkuren, masing-masing dengan parameter split-range. Contoh: split-range=1-20.

5. Gabungkan hasilnya secara berurutan.

Langkah selanjutnya

• Referensi API SelectObject

• Biaya pemrosesan data