4단계: 원장에서 테이블 쿼리

Amazon QLDB 원장에 테이블을 생성하고 데이터를 로드한 후 쿼리를 실행하여 방금 삽입한 차량 등록 데이터를 검토할 수 있습니다. QLDB는 PartiQL을 쿼리 언어로 사용하고 Amazon Ion을 문서 지향 데이터 모델로 사용합니다.

PartiQL은 Ion과 함께 작동하도록 확장된 오픈 소스 SQL 호환 쿼리 언어입니다. PartiQL을 사용하면 익숙한 SQL 연산자를 사용하여 데이터를 삽입, 쿼리 및 관리할 수 있습니다. Amazon Ion은 JSON의 상위 집합입니다. Ion은 정형, 반정형 및 중첩 데이터를 저장하고 처리할 수 있는 유연성을 제공하는 오픈 소스 문서 기반 데이터 형식입니다.

이 단계에서는 SELECT 명령문을 사용하여 vehicle-registration 원장의 테이블에서 데이터를 읽습니다.

주의

인덱싱된 조회 없이 QLDB에서 쿼리를 실행하면 전체 테이블 스캔이 호출됩니다. PartiQL은 SQL과 호환되므로 이러한 쿼리를 지원합니다. 하지만 QLDB의 프로덕션 사용 사례에 대해서는 테이블 스캔을 실행하지 마세요. 테이블 스캔은 동시성 충돌 및 트랜잭션 시간 초과를 포함하여 대규모 테이블에서 성능 문제를 일으킬 수 있습니다.

인덱싱된 필드 또는 문서 ID(예: WHERE indexedField = 123 또는 WHERE indexedField IN (456, 789))에서 동등 연산자를 사용하여 WHERE 조건자 절이 포함된 문을 실행하는 것이 좋습니다. 자세한 내용은 쿼리 성능 최적화을 참조하십시오.

테이블을 쿼리하려면

1. 다음 프로그램(find_vehicles.py)을 사용하여 원장에 있는 사람의 이름으로 등록된 모든 차량을 쿼리하세요.

   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.')

   참고

   먼저 이 프로그램은 이 문서에 대한 Person 테이블을 GovId LEWISR261LL로 쿼리하여 id 메타데이터 필드를 가져옵니다.

   그런 다음 이 문서 id를 외래 키로 사용하여 VehicleRegistration 테이블을 PrimaryOwner.PersonId로 쿼리합니다. 또한 VIN 필드의 Vehicle 테이블과 VehicleRegistration를 조인합니다.

2. 프로그램을 실행하려면 다음 명령을 입력합니다.

   python find_vehicles.py

vehicle-registration 원장의 테이블에 있는 문서를 수정하는 방법에 대한 자세한 내용은 5단계: 원장의 문서 수정을 참조하십시오.