Data Processing in PHP

Transcript

1. Data Processing phpday 2026 https://flow-php.com PHP
2. WHOAMI https://flow-php.com
3. Independent consultant helping companies organize and access their data. https://flow-php.com
4. Fan of Lego & BMD
5. Problem https://flow-php.com
6. How to process a CSV File? https://flow-php.com
7. Orders • order_id – uuid • created_at – datetime • updated_at – datetime • discount – float (nullable) • address – structure{street: string, city: string, zip: string, country: string} • notes – list<string> • items – list<structure{sku: string, quantity: string, price: float}> https://flow-php.com
8. JSON enriched CSV
9. file_get_contents https://flow-php.com
10. fopen fgets str_getcsv fclose https://flow-php.com
11. fopen fgetcsv fclose https://flow-php.com
12. Generators https://flow-php.com
13. Generators (batching) https://flow-php.com
14. What about data types? https://flow-php.com
15. https://flow-php.com
16. Manual Type Casting https://flow-php.com
17. Issues & limitations? • Flexibility • Maintainability • Extendability • Scalability* https://flow-php.com
18. Are there any other solutions? https://flow-php.com
19. Let’s talk about ETL https://flow-php.com
20. ETL • Extract https://flow-php.com
21. ETL • Extract • Transform https://flow-php.com
22. ETL • Extract • Transform • Load https://flow-php.com
23. Extraction • Database • File • API • Streams • Queues / Topics https://flow-php.com
24. Transformation • Filtering • Merging • Cleaning • Grouping • Aggregating • Deduplicating • Sorting • Partitioning https://flow-php.com
25. Loading • Database • File • Stream • Projection • API https://flow-php.com
26. Examples php/scala/python https://flow-php.com
27. How to process a CSV File? https://flow-php.com With PHP
28. Raw PHP https://flow-php.com
29. https://flow-php.com Flow PHP
30. +----------------------+----------------------+----------------------+----------+----------------------+----------------------+----------------------+ | order_id | created_at | updated_at | discount | address | notes | items | +----------------------+----------------------+----------------------+----------+----------------------+----------------------+----------------------+ | 48f7b4b3-48dc-3095-8 | 2024-04-23T01:35:12+ | 2024-04-23T01:35:12+ | | {"street":"56896 Pow | ["Sed cumque sit vol | [{"sku":"SKU_0005"," | | b8670686-1e52-36ee-9 | 2024-04-14T09:00:12+ | 2024-04-14T09:00:12+ | | {"street":"596 Derek | ["Fugiat saepe atque | [{"sku":"SKU_0004"," | | dc052d5e-2b2c-3b2a-9 | 2024-03-03T08:03:02+ | 2024-03-03T08:03:02+ | 40.08 | {"street":"51760 Koe | ["Aliquid voluptatem | [{"sku":"SKU_0004"," | | 6984b96b-6a27-367f-9 | 2024-04-03T16:18:07+ | 2024-04-03T16:18:07+ | | {"street":"9722 Doll | ["Est sit atque quos | [{"sku":"SKU_0004"," | | cb21141a-5494-33ea-9 | 2024-04-25T17:47:49+ | 2024-04-25T17:47:49+ | 2.38 | {"street":"11398 Abs | ["Est atque doloremq | [{"sku":"SKU_0005"," | | c9dc07fc-fa46-3f32-9 | 2024-03-27T12:44:03+ | 2024-03-27T12:44:03+ | | {"street":"78980 Bri | ["Sit aut laudantium | [{"sku":"SKU_0003"," | | 9b828e2d-b509-3485-b | 2024-04-12T06:33:52+ | 2024-04-12T06:33:52+ | | {"street":"6434 Chet | ["Ad consequuntur qu | [{"sku":"SKU_0005"," | | 6f619e18-05aa-306b-8 | 2024-06-10T21:17:45+ | 2024-06-10T21:17:45+ | | {"street":"8038 Crai | ["Dolorum recusandae | [{"sku":"SKU_0005"," | | 7814b135-500f-3137-9 | 2024-05-14T07:39:00+ | 2024-05-14T07:39:00+ | | {"street":"26190 Cor | ["Est quis necessita | [{"sku":"SKU_0005"," | +----------------------+----------------------+----------------------+----------+----------------------+----------------------+----------------------+ 10 rows Output https://flow-php.com
31. How to process a CSV File? With Apache Spark (scala) https://flow-php.com
32. https://flow-php.com
33. +------------------------------------+-------------------------+-------------------------+--------+--------------------------------------------+--------------------------------+----------------------+ |order_id |created_at |updated_at |discount|address |notes |items | +------------------------------------+-------------------------+-------------------------+--------+--------------------------------------------+--------------------------------+----------------------+ |7833e6cb-b123-37f7-bee5-c0fea6dd6787|2024-04-26T06:01:52+00:00|2024-04-26T06:01:52+00:00|2.09 |"{""street"":""64428 Nitzsche Locks"" |""city"":""Lake Deontechester"" |""zip"":""15111"" | |5aa4fb2b-7bc5-3d7c-a9a9-04e88f831b00|2024-01-23T22:53:49+00:00|2024-01-23T22:53:49+00:00|46.87 |"{""street"":""5751 Jamal Drive"" |""city"":""Port Delmer"" |""zip"":""57385"" | |77a297db-b911-3800-a017-7f16502f324f|2024-02-20T00:44:03+00:00|2024-02-20T00:44:03+00:00|29.19 |"{""street"":""831 Murphy Haven"" |""city"":""West Alessandroport""|""zip"":""65846-1195""| |ed030a18-df55-38ce-90a3-c3461032c150|2024-02-15T22:03:47+00:00|2024-02-15T22:03:47+00:00|null |"{""street"":""8617 Lebsack Cape Suite 285""|""city"":""New Leonel"" |""zip"":""43725"" | |d4b7921e-7729-322f-89a1-51e1c5198678|2024-04-12T04:26:42+00:00|2024-04-12T04:26:42+00:00|10.44 |"{""street"":""523 Charlene Mount Apt. 694""|""city"":""Bruenstad"" |""zip"":""40291"" | |ff342e29-a6f8-3df3-b1d0-0adb376557fd|2024-03-24T08:49:27+00:00|2024-03-24T08:49:27+00:00|45.48 |"{""street"":""822 Carmel Common Apt. 560"" |""city"":""Abigailport"" |""zip"":""64470"" | |9771e63f-16e6-311a-a974-a0d60a06fea4|2024-02-14T00:23:18+00:00|2024-02-14T00:23:18+00:00|null |"{""street"":""87471 Jaylon Place"" |""city"":""Cummingsmouth"" |""zip"":""11956-0536""| |eaf137da-c206-3252-a5f5-428b1d4eb4f1|2024-05-03T18:01:13+00:00|2024-05-03T18:01:13+00:00|19.16 |"{""street"":""4494 Kunze Tunnel Apt. 465"" |""city"":""Lake Sabinaland"" |""zip"":""60381-1971""| |64a4ee3d-66e3-3b5e-9830-562c051e6576|2024-01-02T17:57:03+00:00|2024-01-02T17:57:03+00:00|27.79 |"{""street"":""425 Oren Manors"" |""city"":""Lake Vincent"" |""zip"":""69860"" | |24099ba6-9131-3714-84da-d3a59ede3cd1|2024-01-27T22:47:54+00:00|2024-01-27T22:47:54+00:00|45.2 |"{""street"":""328 Daniel Inlet Apt. 768"" |""city"":""Jedediahville"" |""zip"":""77120-2693""| +------------------------------------+-------------------------+-------------------------+--------+--------------------------------------------+--------------------------------+----------------------+ Output https://flow-php.com
34. How to process a CSV File? With Pandas (python)
35. https://flow-php.com
36. order_id created_at updated_at discount address notes items 0 e13d7098-5a78-33... 2024-06-17T19:24... 2024-06-17T19:24... 12.45 {"street":"9742 ... ["Doloremque cum... [{"sku":"SKU_000... 1 947df050-3abb-3f... 2024-02-23T19:18... 2024-02-23T19:18... NaN {"street":"37051... ["Neque dolor et... [{"sku":"SKU_000... 2 6315f9e2-86bf-33... 2024-04-02T11:30... 2024-04-02T11:30... 47.10 {"street":"792 G... ["Et porro fugia... [{"sku":"SKU_000... 3 4cccb632-fade-34... 2024-05-06T00:17... 2024-05-06T00:17... 19.76 {"street":"30203... ["Aliquam saepe ... [{"sku":"SKU_000... 4 82384f8c-9adb-38... 2024-05-10T11:17... 2024-05-10T11:17... NaN {"street":"757 T... ["Beatae nesciun... [{"sku":"SKU_000... 5 e3fcf736-0f8c-3d... 2024-01-25T20:14... 2024-01-25T20:14... NaN {"street":"9088 ... ["Provident quam... [{"sku":"SKU_000... 6 b987a49a-b4c5-37... 2024-06-03T23:22... 2024-06-03T23:22... NaN {"street":"6867 ... ["Quibusdam maio... [{"sku":"SKU_000... 7 663523a9-713b-33... 2024-03-22T23:31... 2024-03-22T23:31... 25.88 {"street":"1577 ... ["In rem maxime ... [{"sku":"SKU_000... 8 6259fa2c-ec68-36... 2024-05-10T10:12... 2024-05-10T10:12... 21.67 {"street":"987 L... ["Voluptatem non... [{"sku":"SKU_000... 9 f7153c83-34b6-37... 2024-02-26T09:20... 2024-02-26T09:20... 18.93 {"street":"2039 ... ["Culpa error re... [{"sku":"SKU_000... Output https://flow-php.com
37. Data Frame https://flow-php.com
38. Data Frame https://flow-php.com
39. Data Frame Size of data frame defines memory consuption Memory = Size of columns in rows * number of rows *simplified version https://flow-php.com
40. What is a transformation? https://flow-php.com
41. Don’t think in objects/functions https://flow-php.com
42. Think in tables, rows, columns and cells https://flow-php.com
43. Just like managing excel https://flow-php.com
44. Dataset - Table https://flow-php.com
45. Data Frame https://flow-php.com
46. Row / Columns https://flow-php.com
47. Row & Column - Cell https://flow-php.com
48. Data Types https://flow-php.com
49. https://flow-php.com Data Types Usually there are two categories of data types
50. https://flow-php.com Physical (primitive) Types • Integer - scalar • String - scalar • Boolean - scalar • Float - scalar • Object • Resource • Null • Enum • Callable • Array
51. https://flow-php.com Logical Types • DateTime • Uuid • Json • List • Map • Structure • XML • XMLElement
52. https://flow-php.com Logical Types Logical types are more specific implementations of native types Different programming languages will provide different logical/native types
53. https://flow-php.com Logical Types • DateTime (object) • Uuid (object) • Json (array) • List (array) • Map (array) • Structure (array) • XML (object) • XMLElement (object)
54. https://flow-php.com Logical Types: List A list is a collection of elements where each element is indexed by its position in the list
55. https://flow-php.com Logical Types: Map A map (also known as a dictionary or associative array) stores key-value pairs, where each key is unique and associated with a single value. * In the PHP, main purpose of map is to guarantee a type of keys and values since regular array is not enforcing them.
56. https://flow-php.com Logical Types: Structure A complex data type grouping multiple fields (physical and logical types)
57. https://flow-php.com Nullability All types can be nullable But not all programming languages handles nulls the same way
58. https://flow-php.com Flow PHP Types Logical Types Native Types
59. What is a transformation? https://flow-php.com
60. https://flow-php.com
61. Transformation is a process of converting, cleansing and structuring data into usable format. https://flow-php.com example of transforming string into Date Time object
62. Data transformations usually happens on a single cell level https://flow-php.com
63. Transform something! https://flow-php.com
64. Type Casting https://flow-php.com
65. https://flow-php.com Before
66. https://flow-php.com Cast Types
67. https://flow-php.com ref - reference
68. https://flow-php.com After
69. Filtering https://flow-php.com
70. https://flow-php.com Only discounted orders
71. Conditional Transformations https://flow-php.com
72. https://flow-php.com Replacing nulls with zeros
73. Sorting https://flow-php.com
74. https://flow-php.com Top 20 recently updated orders
75. How sorting can be memory efficient? https://flow-php.com
76. External sort is a type of sorting algorithms that can handle large amounts of data https://flow-php.com
77. Grouping & Aggregation https://flow-php.com
78. https://flow-php.com Daily Orders Count
79. Joins https://flow-php.com
80. https://flow-php.com Joining orders with products Products Dataset
81. https://flow-php.com Unpack Order Line Items Order Items - List
82. First we need to turn our orders dataset into order line items dataset https://flow-php.com
83. https://flow-php.com Data Flattening Turning nested structures into flat rows
84. https://flow-php.com Unpack Order Line Items
85. https://flow-php.com Expand Order row with 2 items will be turned into two rows with the same order id
86. https://flow-php.com Unpack Unpack will turn each structure element into column
87. https://flow-php.com Full Pipeline
88. https://flow-php.com Output
89. What can we do next? https://flow-php.com
90. Calculate daily revenue Calculate daily profit Find top selling products ... https://flow-php.com
91. Data transformations are applied in steps by adding/replacing columns https://flow-php.com
92. Writing data to different sources https://flow-php.com
93. https://flow-php.com Granular by design composer require flow-php/etl-adapter-parquet composer require flow-php/etl-adapter-json composer require flow-php/etl-adapter-doctrine
94. https://flow-php.com Write to
95. Dataset Schema https://flow-php.com
96. By defining column types we are defining dataset schema https://flow-php.com
97. Schema Definition https://flow-php.com
98. Schema can be used to either validate dataset or to improve extraction performance https://flow-php.com
99. Providing schema to extractor https://flow-php.com
100. https://flow-php.com In this case type casting is unnecessary
101. Using schema to validate rows before loading them to destination https://flow-php.com
102. https://flow-php.com Lets try to validate schema of our joined dataset
103. Will it work? https://flow-php.com
104. https://flow-php.com
105. How it can be fixed? https://flow-php.com
106. https://flow-php.com We can make discount not nullable
107. While working with big datasets and complex transformations schema validation is necessary to guarantee data quality https://flow-php.com
108. Garbage In, Garbage Out https://flow-php.com Why should we care about dataset schema?
109. What are typical use cases of ETL’s? https://flow-php.com
110. https://flow-php.com Use cases Building data storages (lakehouses/warehouses/lakes) Generating reports Consuming API’s Systems synchronizations Building projections Converting datasets formats Initial data analysis
111. Data engineering makes data analysis and data science much easier (cheaper) https://flow-php.com
112. What about performance? https://flow-php.com
113. https://flow-php.com 5 mln rows +/- 3Gb file
114. https://flow-php.com Results
115. https://flow-php.com Was it fast?
116. https://flow-php.com Flow PHP Reach out! https://flow-php.com https://norbert.tech https://www.linkedin.com/in/norberttech/ https://x.com/norbert_tech https://phpc.social/@norbert
117. That’s all for today! https://flow-php.com Questions?