sql-server - Fitbit 数据导出 - 创建数据仓库

Question

我计划为教育目的创建一个 Fitbit 数据仓库，网上似乎没有任何专门针对 Fitbit 数据的资料。

面临的几个问题：

1. 您一次只能从 Fitbit 网站导出 1 个月的数据（最大值）。我的计划是一次将一个月的数据放入一个文件夹中，并分别读取这些文件。

2. 您可以通过 CSV 或 .XLS 导出数据。XLS 的问题是每月的每一天都会为食物日志创建一个单独的表，然后需要将其合并到一个临时表中。CSV 的问题是每个文件只有一张纸，其中包含所有数据：CSV Layout

然后，我将使用 SSIS 将数据加载到 SQL Server 数据库中以进行报告。

哪种方法更适合使用 .XLS 格式或 CSV 导出数据？

编辑：如何以这种格式将 CSV 文件加载到 SSIS 中？

CSV 布局将是这样的：

身体,,,,,,,,, 日期,体重,BMI,脂肪,,,,,, 01/06/2018,71.5,23.29,15,,,,,, 02/06/2018,71.5,23.29, 15,,,,,, 03/06/2018,71.5,23.29,15,,,,,, 04/06/2018,71.5,23.29,15,,,,,,, 05/06/2018,71.5,23.29 ,15,,,,,, 06/06/2018,71.5,23.29,15,,,,,, 07/06/2018,71.5,23.29,15,,,,,,, 08/06/2018,71.5, 23.29,15,,,,,, 09/06/2018,71.5,23.29,15,,,,,, 10/06/2018,71.5,23.29,15,,,,,, 11/06/2018,71.5 ,23.29,15,,,,,, 12/06/2018,71.5,23.29,15,,,,,, 13/06/2018,71.5,23.29,15,,,,,, 14/06/2018, 71.5,23.29,15,,,,,, 15/06/2018,71.5,23.29,15,,,,,, 16/06/2018,71.5,23.29,15,,,,,, 17/06/2018 ,71.5,23.29,15,,,,,, 18/06/2018,71.5,23.29,15,,,,,, 19/06/2018,71.5,23.29,15,,,,,, 20/06/ 2018,71.5,23.29,15,,,,,, 21/06/2018,71.5,23.29,15,,,,,, 22/06/2018,71.5,23.29,15,,,,,, 23/06 /2018,71.5,23.29,15,,,,,, 24/06/2018,71.5,23.29,15,,,,,, 25/06/2018,71.5,23.29,15,,,,,,, 26/ 06/2018,71.5,23.29,15,,,,,,, 27/06/2018,71.5,23.29,15,,,,,, 28/06/2018,71.5,23.29,15,,,,,,, 29/06/2018,72.8,23.72,15,,,,,, 30/06/2018 ,72.95,23.77,15,,,,,,,,,,,,,,,,

食物,,,,,,,,, 日期,卡路里,,,,,,,, 01/06/2018,0,,,,,,,,, 02/06/2018,0,,,,,, ,, 03/06/2018,0,,,,,,,, 04/06/2018,0,,,,,,,,, 05/06/2018,0,,,,,,,, 06/06 /2018,0,,,,,,,, 07/06/2018,0,,,,,,,, 08/06/2018,0,,,,,,,, 09/06/2018,0, ,,,,,,, 10/06/2018,0,,,,,,,, 11/06/2018,0,,,,,,,, 12/06/2018,0,,,,,, ,, 13/06/2018,100,,,,,,,, 14/06/2018,0,,,,,,,, 15/06/2018,0,,,,,,,, 16/06 /2018,0,,,,,,,, 17/06/2018,0,,,,,,,, 18/06/2018,0,,,,,,,, 19/06/2018,0, ,,,,,,, 20/06/2018,0,,,,,,,, 21/06/2018,0,,,,,,,, 22/06/2018,0,,,,,, ,, 23/06/2018,0,,,,,,,, 24/06/2018,0,,,,,,,, 25/06/2018,0,,,,,,,, 26/06 /2018,0,,,,,,,,, 27/06/2018,"1,644",,,,,,,, 28/06/2018,"2,390",,,,,,,, 29/06/ 2018,981,,,,,,,, 30/06/2018,0,,,,,,,,

例如，“Foods”是表名，“Date”和“Calories In”是列名。“01/06/2018”是日期，“0”是“卡路里”等等。

标签： sql-serverssisdata-warehousefitbit

Answer 1

棘手，我刚刚提取了我的 fitbit 数据，因为这激起了我的好奇心。那个csv很乱。您基本上在一个文件中具有混合文件格式。这在 SSIS 中不会直截了当。XLS 格式和你提到的每天在工作表上标记的食物日志一样，SSIS 不会喜欢这种变化。

CSV： XLS：

我在 CSV 中看到了几个选项。

从 Fitbit 单独导出

我看到您可以选择要包含在导出中的数据：身体、食物、活动、睡眠。

1. 单独执行每个导出，保存每个文件，并使用它是什么类型的数据的前缀。
2. 然后为每个单独的文件格式构建具有多个 foreach 循环和数据流任务的 SSIS。

这样做可以，但在必须从 Fitbit 导出数据时会是一项繁琐的工作。

处理包含所有数据的一个文件

这个选项你必须要有创意，因为格式是混合的，并且你有不同的列定义等部分。

一种选择是创建一个暂存表，其中包含与哪个部分最多的列一样多的列，这看起来可能是“活动”。为每列指定一个通用名称 Column1,Column2 并将它们全部设为 VARCHAR。

由于我们混合了“格式”并且并非所有数据类型都会排列，我们只需要先获取所有数据，然后再进行转换。

从那里您可以构建一个数据流和平面文件源，还可以添加行号，因为我们稍后需要整理每个数据部分的位置。

在为您的源构建文件连接时，您必须手动添加所有列，因为文件中的第一行数据不包含每个字段的所有逗号，SSIS 将无法检测到所有列。手动添加所需的列数，还要确保：

• 文本限定符 = "
• 标题行分隔符 = {LF}
• 行分隔符 = {LF}
• 列分隔符 = ,

这应该让您将数据加载到数据库中，至少加载到阶段表中。从那里你需要使用一堆 T-SQL 将数据的每个“部分”归零，然后从那里解析、转换和加载。

我做的小测试我刚刚有表调用TestTable：

CREATE TABLE [dbo].[TestTable](
    [LineNumber] [INT] NULL,
    [Column1] [VARCHAR](MAX) NULL,
    [Column2] [VARCHAR](MAX) NULL,
    [Column3] [VARCHAR](MAX) NULL,
    [Column4] [VARCHAR](MAX) NULL,
    [Column5] [VARCHAR](MAX) NULL,
    [Column6] [VARCHAR](MAX) NULL,
    [Column7] [VARCHAR](MAX) NULL,
    [Column8] [VARCHAR](MAX) NULL,
    [Column9] [VARCHAR](MAX) NULL
)

数据流并连接文件源：

执行数据流，然后我将数据加载为：

从那里我制定了一些 T-SQL 来获取数据的每个“部分”。这是一个示例，展示了如何过滤到“食物”部分：

DECLARE @MaxLine INT = (
                           SELECT MAX([LineNumber])
                           FROM   [TestTable]
                       );

--Something like this, using a sub query that gets you starting and ending line numbers for each section.
--Doing the conversion of what column that section of data ended up in.
SELECT     CONVERT(DATE, [a].[Column1]) AS [Date]
         , CONVERT(BIGINT, [a].[Column2]) AS [CaloriesIn]
FROM       [TestTable] [a]
INNER JOIN (
               --Something like this to build out starting and ending line number for each section
               SELECT [Column1]
                    , [LineNumber] + 2 AS [StartLineNumber] --We add 2 here as the line that start the data in a section is 2 after its "heading"
                    , LEAD([LineNumber], 1, @MaxLine) OVER ( ORDER BY [LineNumber] )
                      - 1 AS [EndLineNumber]
               FROM   [TestTable]
               WHERE  [Column1] IN ( 'Body', 'Foods', 'Activities' ) --Each of the sections of data
           ) AS [Section]
    ON [a].[LineNumber]
       BETWEEN [Section].[StartLineNumber] AND [Section].[EndLineNumber]
WHERE      [Section].[Column1] = 'Foods'; --Then just filter on what sectoin you want.

这反过来又给了我以下信息：

解析该数据可能还有其他选项，但这应该提供一个很好的起点，并了解这个特定的 CSV 文件有多棘手。

至于 XLS 选项，这对于除食品日志之外的所有部分都是直截了当的。您基本上会设置一个 excel 文件连接，并且每个工作表都将是数据流源中的一个“表”，并且每个工作表都有单独的数据流。

但是食物日志呢？一旦这些改变并且你进入下个月或者 SSIS 会吓坏，错误，可能会抱怨元数据。

一项明显的解决方法是手动操作 excel 并将它们全部合并到一个“食物日志”表中，然后再通过 SSIS 运行它。不理想，因为您可能想要完全自动化的东西。

我不得不修补它。也许是一个脚本任务和一些 C# 代码将所有这些工作表合并为一个，从每个工作表名称中解析日期，并在数据流加载之前将其附加到数据中。也许有可能。

无论您查看哪种格式，Fitbit 导出的两个文件似乎都存在挑战。