Using the Usage Analytics API to retrieve data into an SQL Server database
I'm looking for a way to retrieve the Usage Analytics data directly into an SQL Server database. Coveo Customer Support recommended the Usage Analytics API.
Does anyone have experience using the Usage Analytics API to retrieve data into an SQL Server database? I haven't used an API before, though have been reading about them online (including tutorials). Relative to SQL Server, it looks like this web page is relevant to my interest -- but I'm feeling like I need pointers from someone with more experience, able to give advice on how to get started.
Is this something that you need to do regularly or only once?
If you only need to do this once, you could simply manually do an Export https://onlinehelp.coveo.com/en/cloud/exportinganalyticsdata.htm
You could then import the CSV into a SQL Server Database. There are various ways to do this such as http://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server
If you need to do this periodically, you'll need to get familiar with the API. I'd use the API to launch an Export, and then download the ZIP, extract it and push it into SQL Server. This is not trivial and will require some programming experience.
As Guillaume said, you need to use the API to do this. The call you.ll want to use is https://usageanalytics.coveo.com/docs/#!/v15%2Fexports%2F/export. The documentation should explain the parameters. Note that for optimal performances, incremental exports are the way to go (i.e. you don't need to re-export data that you have already exported).
Once you created the export, you can use the following API call to download it : https://usageanalytics.coveo.com/docs/#!/v15%2Fexports%2F/getExport. With the parameter "redirect" to false, you will get information on the export. Once the status is AVAILABLE, you can use the same call with "redirect" to true to download a zip file containing the different CSV files of the export.
After that, you need to import the data you want into your database. Since I am not familiar with SQL Server, I can't help you with that, but there are plenty of resources available online that should point you in the right direction.
Hope this helps, Jonathan