# Connecting to QuickBooks QuickBooks is a leading accounting application for small and medium-sized businesses. QuickBooks accounting applications date back to the 1980s as one of the first products by Intuit, and accordingly was originally desktop software. Today, QuickBooks offers several accounting and business financial applications as both installable software and cloud-based SaaS software. As a QuickBooks user, you can connect AWS Glue to your QuickBooks account. Then, you can use QuickBooks as a data source in your ETL jobs. Run these jobs to transfer data between QuickBooks and AWS services or other supported applications. **Topics** + [AWS Glue support for QuickBooks](quickbooks-support.md) + [Policies containing the API operations for creating and using connections](quickbooks-configuring-iam-permissions.md) + [Configuring QuickBooks](quickbooks-configuring.md) + [Configuring QuickBooks connections](quickbooks-configuring-connections.md) + [Reading from QuickBooks entities](quickbooks-reading-from-entities.md) + [QuickBooks connection options](quickbooks-connection-options.md) + [Limitations and notes for QuickBooks connector](quickbooks-connector-limitations.md) # AWS Glue support for QuickBooks AWS Glue supports QuickBooks as follows: **Supported as a source?** Yes. You can use AWS Glue ETL jobs to query data from QuickBooks. **Supported as a target?** No. **Supported QuickBooks API versions** The following QuickBooks API versions are supported: + v3 # Policies containing the API operations for creating and using connections The following sample policy describes the required AWS IAM permissions for creating and using connections. If you are creating a new role, create a policy that contains the following: ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "glue:ListConnectionTypes", "glue:DescribeConnectionType", "glue:RefreshOAuth2Tokens", "glue:ListEntities", "glue:DescribeEntity" ], "Resource": "*" } ] } ``` ------ If you don't want to use the above method, alternatively use the following managed IAM policies: + [AWSGlueServiceRole](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole) – Grants access to resources that various AWS Glue processes require to run on your behalf. These resources include AWS Glue, Amazon S3, IAM, CloudWatch Logs, and Amazon EC2. If you follow the naming convention for resources specified in this policy, AWS Glue processes have the required permissions. This policy is typically attached to roles specified when defining crawlers, jobs, and development endpoints. + [AWSGlueConsoleFullAccess](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess) – Grants full access to AWS Glue resources when an identity that the policy is attached to uses the AWS Management Console. If you follow the naming convention for resources specified in this policy, users have full console capabilities. This policy is typically attached to users of the AWS Glue console. # Configuring QuickBooks Before you can use AWS Glue to transfer data from QuickBooks, you must meet these requirements: ## Minimum requirements The following are minimum requirements: + You have a QuickBooks account. + Your QuickBooks account is enabled for API access. For more information, see the following topics in QuickBooks documentation: + [Create an Intuit Account](https://quickbooks.intuit.com/learn-support/en-us/help-article/account-management/create-intuit-user-account/L62kSFEOM_US_en_US) + [Create and start developing your app](https://developer.intuit.com/app/developer/qbo/docs/get-started/start-developing-your-app) If you meet these requirements, you’re ready to connect AWS Glue to your QuickBooks account. For typical connections, you don't need do anything else in QuickBooks. # Configuring QuickBooks connections QuickBooks supports the AUTHORIZATION\$1CODE grant type for OAuth2. The grant type determines how AWS Glue communicates with QuickBooks to request access to your data. + This grant type is considered "three-legged" OAuth as it relies on redirecting users to a third-party authorization server to authenticate the user. It is used when creating connections via the AWS Glue console. + Users may still opt to create their own connected app in QuickBooks and provide their own client ID and client secret when creating connections through the AWS Glue console. In this scenario, they will still be redirected to QuickBooks to login and authorize AWS Glue to access their resources. + This grant type results in a refresh token and access token. The access token is short lived, and may be refreshed automatically without user interaction using the refresh token. + For public QuickBooks documentation on creating a connected app for Authorization Code OAuth flow, see [Set up OAuth 2.0](https://developer.intuit.com/app/developer/qbo/docs/develop/authentication-and-authorization/oauth-2.0). To configure a QuickBooks connection: 1. In AWS Glue Glue Studio, create a connection under **Data Connections** by following the steps below: 1. When selecting a **Connection type**, select QuickBooks. 1. Provide the instance URL and company ID of the QuickBooks instance you want to connect to. 1. Select the AWS IAM role which AWS Glue can assume and has permissions for following actions: ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "secretsmanager:DescribeSecret", "secretsmanager:GetSecretValue", "secretsmanager:PutSecretValue", "ec2:CreateNetworkInterface", "ec2:DescribeNetworkInterfaces", "ec2:DeleteNetworkInterface" ], "Resource": "*" } ] } ``` ------ 1. Select the `secretName` which you want to use for this connection in AWS Glue to put the tokens. 1. Select the network options if you want to use your network. 1. Grant the IAM role associated with your AWS Glue job permission to read `secretName`. # Reading from QuickBooks entities **Prerequisite** A QuickBooks object you would like to read from. **Supported entities for source**: | Entity | Can be filtered | Supports limit | Supports Order by | Supports Select \$1 | Supports partitioning | | --- | --- | --- | --- | --- | --- | | Account | Yes | Yes | Yes | Yes | Yes | | Bill | Yes | Yes | Yes | Yes | Yes | | Company Info | No | No | No | Yes | No | | Customer | Yes | Yes | Yes | Yes | Yes | | Employee | Yes | Yes | Yes | Yes | Yes | | Estimate | Yes | Yes | Yes | Yes | Yes | | Invoice | Yes | Yes | Yes | Yes | Yes | | Item | Yes | Yes | Yes | Yes | Yes | | Payment | Yes | Yes | Yes | Yes | Yes | | Preferences | No | No | No | Yes | No | | Profit and Loss | Yes | No | No | Yes | No | | Tax Agency | Yes | Yes | Yes | Yes | Yes | | Vendors | Yes | Yes | Yes | Yes | Yes | **Example**: ``` QuickBooks_read = glueContext.create_dynamic_frame.from_options( connection_type="quickbooks", connection_options={ "connectionName": "connectionName", "ENTITY_NAME": "Account", "API_VERSION": "v3" } ``` **QuickBooks entity and field details**: For more information about the entities and field details see: + [Account](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/account) + [Bill](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/bill) + [CompanyInfo](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/companyinfo) + [Customer](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/customer) + [Employee](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/employee) + [Estimate](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/estimate) + [Invoice](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/invoice) + [Item](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/item) + [Payment](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/payment) + [Preferences](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/preferences) + [ProfitAndLoss](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/profitandloss) + [TaxAgency](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/taxagency) + [Vendor](https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/vendor) ## Partitioning queries **Field-based partitioning**: In QuickBooks, the Integer and DateTime datatype fields support field-based partitioning. You can provide the additional Spark options `PARTITION_FIELD`, `LOWER_BOUND`, `UPPER_BOUND`, and `NUM_PARTITIONS` if you want to utilize concurrency in Spark. With these parameters, the original query would be split into `NUM_PARTITIONS` number of sub-queries that can be executed by Spark tasks concurrently. + `PARTITION_FIELD`: the name of the field to be used to partition the query. + `LOWER_BOUND`: an **inclusive** lower bound value of the chosen partition field. For the Datetime field, we accept the Spark timestamp format used in Spark SQL queries. Examples of valid value: ``` "2024-05-07T02:03:00.00Z" ``` + `UPPER_BOUND`: an **exclusive** upper bound value of the chosen partition field. + `NUM_PARTITIONS`: the number of partitions. Example: ``` QuickBooks_read = glueContext.create_dynamic_frame.from_options( connection_type="quickbooks", connection_options={ "connectionName": "connectionName", "REALMID": "12345678690123456789", "ENTITY_NAME": "Account", "API_VERSION": "v3", "PARTITION_FIELD": "MetaData_CreateTime" "LOWER_BOUND": "2023-09-07T02:03:00.000Z" "UPPER_BOUND": "2024-05-07T02:03:00.000Z" "NUM_PARTITIONS": "10" } ``` **Record-based partitioning**: The original query is splitinto `NUM_PARTITIONS` number of sub-queries that can be executed by Spark tasks concurrently: + `NUM_PARTITIONS`: the number of partitions. Example: ``` QuickBooks_read = glueContext.create_dynamic_frame.from_options( connection_type="quickbooks", connection_options={ "connectionName": "connectionName", "REALMID": "1234567890123456789", "ENTITY_NAME": "Bill", "API_VERSION": "v3", "NUM_PARTITIONS": "10" } ``` # QuickBooks connection options The following are connection options for QuickBooks: + `ENTITY_NAME`(String) - (Required) Used for Read. The name of your object in QuickBooks. + `INSTANCE_URL`(String) - (Required) A valid QuickBooks instance URL. + `API_VERSION`(String) - (Required) Used for Read. QuickBooks Rest API version you want to use. + `REALM_ID`(String) - An ID that identifies an individual QuickBooks Online company where you send requests. + `SELECTED_FIELDS`(List) - Default: empty(SELECT \$1). Used for Read. Columns you want to select for the object. + `FILTER_PREDICATE`(String) - Default: empty. Used for Read. It should be in the Spark SQL format. + `QUERY`(String) - Default: empty. Used for Read. Full Spark SQL query. + `PARTITION_FIELD`(String) - Used for Read. Field to be used to partition query. + `LOWER_BOUND`(String)- Used for Read. An inclusive lower bound value of the chosen partition field. + `UPPER_BOUND`(String) - Used for Read. An exclusive upper bound value of the chosen partition field. + `NUM_PARTITIONS`(Integer) - Default: 1. Used for Read. Number of partitions for read. # Limitations and notes for QuickBooks connector The following are limitations or notes for the QuickBooks connector: + In the `taxAgency` API, order by filtering is not working as expected.