{"type":"doc","content":[{"type":"paragraph","content":[{"text":"You have the choice to export to a file(Excel, CSV, JSON, etc.) or a SQL database. Exporting to Excel files and Markdown is primarily used for testing. Exporting to SQL is more commonly used. ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"You can specify batch sizes by adding a --batch-size=number at the end of your command.","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"“Checkpoints” enable incremental exports. A checkpoint will get set after each batch of data that is fetched from the API. The next export will start at the last checkpoint. Checkpoints are only created when exporting to a SQL database. ","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export to an Excel file","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to an Excel file allows you to get a full results-set without setting up a local database. When you export to Excel, there are no checkpoints. It’s all or nothing. If the query fails, the Excel file is not created.","type":"text"}]},{"type":"paragraph","content":[{"text":"You can run a data export and save it to an Excel file with the DET. Excel files are great for one-time data exports or if you want to use them to analyze and visualize the data. Excel is also a great option if you are not familiar with working with databases.","type":"text"}]},{"type":"paragraph","content":[{"text":"The downside to Excel files is that they can’t handle large datasets. You can also only do once-off export to an Excel file; there are no “checkpoints” created for incremental updates when the DET is run. ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note: You can specify batch sizes by adding a --batch-size=number at the end of your command.","type":"text"}]},{"type":"paragraph","content":[{"text":"When exporting to Excel, the DET will fetch data from the API in batches, and then when the final batch is processed, the output will be written to the Excel file.","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export to a Database","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to a database supports incremental updates. When you export to a database, you can have millions of rows of data, so the DET handles it in batches. ","type":"text"}]},{"type":"paragraph","content":[{"text":"After each successful batch, the tool writes a checkpoint to a database table that the DET maintains.","type":"text"}]},{"type":"paragraph","content":[{"text":"By default, each time the commcare-export command runs, it will only import results that were modified after the most recently imported form or case. This means that, for very large datasets, you do not need to export a full set of results every time. ","type":"text"}]},{"type":"paragraph","content":[{"text":"This can be an incredibly powerful feature for a data pipeline. (See more information at ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/BibKfw"}},{"text":").","type":"text"}]},{"type":"paragraph","content":[{"text":"You can configure the DET to pull data on a schedule to automatically keep your data up to date or run it as a one-time data dump.","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to SQL allows you to clean and manipulate the data before you integrate it with data visualization tools like Power BI or Tableau.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"If you are looking to export into a SQL database, you must have this database set up, and you should know how to provide a URL for it. The DET will create the necessary tables and columns in the SQL database based on the properties contained in the query file.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"By choosing \"--output-format sql\" and \"--output DATABASE_URL\" in your command, you can save the output to a SQL database. ","type":"text"}]},{"type":"paragraph","content":[{"text":"The term DATABASE_URL is a standard URL format for databases in these commands. If, for example, you have a PostgreSQL database called “registrations” that is hosted on ","type":"text"},{"text":"db.example.com","type":"text","marks":[{"type":"link","attrs":{"href":"http://db.example.com/"}}]},{"text":" that you would like to save into instead, the URL might look like ","type":"text"},{"text":"postgresql://db.example.com/registrations","type":"text","marks":[{"type":"link","attrs":{"href":"#"}}]},{"text":" (you may need to include username and password in the URL).","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Checkpoints","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The first time you run a DET query to a database, it will get all the records that exist and then set the checkpoint at the end.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The next time you run the query, it will just start from the last checkpoint and get all the cases modified after that time or forms submitted after that time.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This checkpoint data is stored in a table called “commcare-export-runs” in the database.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If you change the query file, the DET will know that it’s a new query file because the MD5 hash will not match. This triggers a full refresh of all data in the domain. See ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/OTDKfw"}},{"text":" for more information.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A checkpoint will get set after each batch of data that is fetched from the API. Using the --batch-size=number command-line argument will change how much data is fetched in each batch and, therefore, the frequency of the checkpoints.","type":"text"}]}]}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"Technical information for exporting to SQL","type":"text"}]},{"type":"paragraph","content":[{"text":"Note that a field named id is required for use with a SQL database.","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to SQL requires installing additional python libraries. These are listed in the table below.","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Supported Databases","type":"text","marks":[{"type":"em"}]}]},{"type":"table","attrs":{"layout":"full-width","width":760.0,"localId":"afbcde92-752b-4a73-8f3c-25cb49223dcf"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[100.0]},"content":[{"type":"paragraph","content":[{"text":"Database name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[378.0]},"content":[{"type":"paragraph","content":[{"text":"URL format","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"Maximum field name length","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Dependencies","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[100.0]},"content":[{"type":"paragraph","content":[{"text":"MSSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[378.0]},"content":[{"type":"paragraph","content":[{"text":"mssql+pyodbc://:@/?driver=ODBC+Driver+18+for+SQL+Server","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"128","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'pyodbc' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install pyodbc","type":"text"}]},{"type":"paragraph","content":[{"text":"Also requires the SQL Server drivers to be installed. ","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[100.0]},"content":[{"type":"paragraph","content":[{"text":"Azure MSSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[378.0]},"content":[{"type":"paragraph","content":[{"text":"mssql+pyodbc:///?odbc_connect= (see: ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/lErKfw"}},{"text":" )","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"128","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Same as MSSQL above","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[100.0]},"content":[{"type":"paragraph","content":[{"text":"MySQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[378.0]},"content":[{"type":"paragraph","content":[{"text":"mysql+pymysql://:@/?charset=utf8mb4","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"64","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'pymysql' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install pymysql","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[100.0]},"content":[{"type":"paragraph","content":[{"text":"Oracle","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[378.0]},"content":[{"type":"paragraph","content":[{"text":"oracle+cx_oracle://:@/","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"128","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'cx_oracle' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install cx_oracle","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[100.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[378.0]},"content":[{"type":"paragraph","content":[{"text":"postgresql://:@/","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"63","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'psycopg2' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install psycopg2","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"type":"hardBreak"},{"text":"Data types","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Data Type Detection","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"By default, the data export tool will choose the data type of the SQL column based on the incoming data. Strings, integers, and boolean values are supported.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Columns are created dynamically when data is received, and the types of columns may be updated dynamically as new data comes in to make it compatible. This means that until the export tool sees a non-null value for the column, it will not create the column in SQL.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"You can also use the command-line option \"","type":"text"},{"text":"--strict-types","type":"text","marks":[{"type":"em"}]},{"text":"\" which will prevent SQL column types from being changed. This may result in errors if incompatible data is received from CommCare HQ.","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":6},"content":[{"text":"Explicit Data Types","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"You can also explicitly set data types by adding a column with the heading \"Data Type\". Allowed values for this column are:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"86d33eaf-1ef2-4491-bee3-d4bd70efb469"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Value","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"text","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"boolean","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"datetime","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"integer","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"json (Postgres Databases only)","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"If you specify a data type column, then the column will be created when the table is first created instead of dynamically based on data. Note that if you use explicit data types, you are responsible for ensuring the data going into the database is valid for that column. Specifying these conversions may be necessary for some data types - especially when converting string/text values to other types.","type":"text"},{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Data Type Conversion","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"To convert data of one type to another type before it goes into the database, you can provide the following functions in the Map Via column:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"934b9bbb-9109-464b-9e89-d17c9d11ab86"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Function name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"str2bool","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Convert a string value to a boolean. The following values result in a True output:","type":"text"}]},{"type":"paragraph","content":[{"text":"'true', 't', '1' (case doesn't matter)","type":"text"}]},{"type":"paragraph","content":[{"text":"If the value is already a boolean, it will be output without change. All other values result in a False output.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"bool2int","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"This supports converting boolean values and booleans represented as strings to the integers 1 or 0.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"str2num","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Convert a string representation of a number to a number.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"str2date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Convert a string representation of a date or timestamp to a date or timestamp.","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export to Markdown","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to “Markdown” gives you the results in the command-line. Markdown is primarily used for testing. ","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":3},"marks":[{"type":"alignment","attrs":{"align":"end"}}],"content":[{"text":"Next Page: ","type":"text"},{"text":"Authentication when using DET >","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Authentication+when+using+DET"}}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"pagetree","parameters":{"macroParams":{"_parentId":{"value":"2143947378"},"root":{"value":"CommCare Data Export Tool (DET)"}},"macroMetadata":{"macroId":{"value":"fc3907c6-42e1-4204-8fda-b9a6acc09e15"},"schemaVersion":{"value":"1"},"title":"Page Tree"}},"localId":"6ca36ab7-8011-41d1-b017-0db06802acb6"}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}],"version":1}

Browser not supported