Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà. # Passaggio 4: interrogare le tabelle in un libro mastro **Importante** Avviso di fine del supporto: i clienti esistenti potranno utilizzare Amazon QLDB fino alla fine del supporto, il 31/07/2025. Per ulteriori dettagli, consulta [Migrare un registro Amazon QLDB su Amazon Aurora PostgreSQL](https://aws.amazon.com/blogs/database/migrate-an-amazon-qldb-ledger-to-amazon-aurora-postgresql/). Dopo aver creato tabelle in un registro Amazon QLDB e averle caricate con i dati, puoi eseguire query per rivedere i dati di immatricolazione dei veicoli che hai appena inserito. QLDB [utilizza](ql-reference.md) PartiQL come linguaggio di interrogazione [e Amazon](ion.md) Ion come modello di dati orientato ai documenti. PartiQL è un linguaggio di query open source compatibile con SQL che è stato esteso per funzionare con Ion. Con PartiQL, puoi inserire, interrogare e gestire i tuoi dati con operatori SQL familiari. Amazon Ion è un superset di JSON. Ion è un formato di dati open source basato su documenti che offre la flessibilità di archiviazione ed elaborazione di dati strutturati, semistrutturati e annidati. In questo passaggio, si utilizzano `SELECT` le istruzioni per leggere i dati dalle tabelle del registro. `vehicle-registration` **avvertimento** Quando si esegue una query in QLDB senza una ricerca indicizzata, viene richiamata una scansione completa della tabella. PartiQL supporta tali query perché è compatibile con SQL. Tuttavia, *non* eseguire scansioni di tabelle per casi d'uso di produzione in QLDB. Le scansioni delle tabelle possono causare problemi di prestazioni su tabelle di grandi dimensioni, inclusi conflitti di concorrenza e timeout delle transazioni. Per evitare le scansioni delle tabelle, è necessario eseguire istruzioni con una clausola di `WHERE` predicato utilizzando un operatore di *uguaglianza* su un campo indicizzato o un ID di documento; ad esempio, o. `WHERE indexedField = 123` `WHERE indexedField IN (456, 789)` Per ulteriori informazioni, consulta [Ottimizzazione delle prestazioni delle query](working.optimize.md). **Per interrogare le tabelle** 1. Usa il seguente programma (`find_vehicles.py`) per interrogare tutti i veicoli registrati con una persona nel tuo registro. ------ #### [ 3.x ] ``` # Copyright 2019 Amazon.com, Inc. or its affiliates. All Rights Reserved. # SPDX-License-Identifier: MIT-0 # # Permission is hereby granted, free of charge, to any person obtaining a copy of this # software and associated documentation files (the "Software"), to deal in the Software # without restriction, including without limitation the rights to use, copy, modify, # merge, publish, distribute, sublicense, and/or sell copies of the Software, and to # permit persons to whom the Software is furnished to do so. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, # INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A # PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT # HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION # OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE # SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # # This code expects that you have AWS credentials setup per: # https://boto3.amazonaws.com/v1/documentation/api/latest/guide/quickstart.html from logging import basicConfig, getLogger, INFO from pyqldbsamples.model.sample_data import get_document_ids, print_result, SampleData from pyqldbsamples.constants import Constants from pyqldbsamples.connect_to_ledger import create_qldb_driver logger = getLogger(__name__) basicConfig(level=INFO) def find_vehicles_for_owner(driver, gov_id): """ Find vehicles registered under a driver using their government ID. :type driver: :py:class:`pyqldb.driver.qldb_driver.QldbDriver` :param driver: An instance of the QldbDriver class. :type gov_id: str :param gov_id: The owner's government ID. """ document_ids = driver.execute_lambda(lambda executor: get_document_ids(executor, Constants.PERSON_TABLE_NAME, 'GovId', gov_id)) query = "SELECT Vehicle FROM Vehicle INNER JOIN VehicleRegistration AS r " \ "ON Vehicle.VIN = r.VIN WHERE r.Owners.PrimaryOwner.PersonId = ?" for ids in document_ids: cursor = driver.execute_lambda(lambda executor: executor.execute_statement(query, ids)) logger.info('List of Vehicles for owner with GovId: {}...'.format(gov_id)) print_result(cursor) def main(ledger_name=Constants.LEDGER_NAME): """ Find all vehicles registered under a person. """ try: with create_qldb_driver(ledger_name) as driver: # Find all vehicles registered under a person. gov_id = SampleData.PERSON[0]['GovId'] find_vehicles_for_owner(driver, gov_id) except Exception as e: logger.exception('Error getting vehicles for owner.') raise e if __name__ == '__main__': main() ``` ------ #### [ 2.x ] ``` # Copyright 2019 Amazon.com, Inc. or its affiliates. All Rights Reserved. # SPDX-License-Identifier: MIT-0 # # Permission is hereby granted, free of charge, to any person obtaining a copy of this # software and associated documentation files (the "Software"), to deal in the Software # without restriction, including without limitation the rights to use, copy, modify, # merge, publish, distribute, sublicense, and/or sell copies of the Software, and to # permit persons to whom the Software is furnished to do so. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, # INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A # PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT # HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION # OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE # SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # # This code expects that you have AWS credentials setup per: # https://boto3.amazonaws.com/v1/documentation/api/latest/guide/quickstart.html from logging import basicConfig, getLogger, INFO from pyqldbsamples.model.sample_data import get_document_ids, print_result, SampleData from pyqldbsamples.constants import Constants from pyqldbsamples.connect_to_ledger import create_qldb_session logger = getLogger(__name__) basicConfig(level=INFO) def find_vehicles_for_owner(transaction_executor, gov_id): """ Find vehicles registered under a driver using their government ID. :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor` :param transaction_executor: An Executor object allowing for execution of statements within a transaction. :type gov_id: str :param gov_id: The owner's government ID. """ document_ids = get_document_ids(transaction_executor, Constants.PERSON_TABLE_NAME, 'GovId', gov_id) query = "SELECT Vehicle FROM Vehicle INNER JOIN VehicleRegistration AS r " \ "ON Vehicle.VIN = r.VIN WHERE r.Owners.PrimaryOwner.PersonId = ?" for ids in document_ids: cursor = transaction_executor.execute_statement(query, ids) logger.info('List of Vehicles for owner with GovId: {}...'.format(gov_id)) print_result(cursor) if __name__ == '__main__': """ Find all vehicles registered under a person. """ try: with create_qldb_session() as session: # Find all vehicles registered under a person. gov_id = SampleData.PERSON[0]['GovId'] session.execute_lambda(lambda executor: find_vehicles_for_owner(executor, gov_id), lambda retry_attempt: logger.info('Retrying due to OCC conflict...')) except Exception: logger.exception('Error getting vehicles for owner.') ``` ------ **Nota** Innanzitutto, questo programma interroga la `Person` tabella del documento per ottenere il relativo `GovId LEWISR261LL` campo di `id` metadati. Quindi, utilizza questo documento `id` come chiave esterna per interrogare la `VehicleRegistration` tabella. `PrimaryOwner.PersonId` Inoltre si unisce `VehicleRegistration` alla `Vehicle` tabella sul `VIN` campo. 1. Per eseguire il programma, immetti il comando seguente: ``` python find_vehicles.py ``` Per ulteriori informazioni sulla modifica dei documenti nelle tabelle del `vehicle-registration` libro mastro, consulta. [Fase 5: Modificare i documenti in un libro mastro](getting-started.python.step-5.md)