{"id":991926,"date":"2025-05-15T09:47:20","date_gmt":"2025-05-15T09:47:20","guid":{"rendered":"https:\/\/mynavitechtus.com\/?p=991926"},"modified":"2025-07-25T03:32:51","modified_gmt":"2025-07-25T03:32:51","slug":"experience-handling-large-csv-uploads-5gb-with-complex-validation-2","status":"publish","type":"post","link":"https:\/\/mynavitechtus.com\/ja\/experience-handling-large-csv-uploads-5gb-with-complex-validation-2\/","title":{"rendered":"\u8907\u96d1\u306a\u691c\u8a3c\u3092\u4f34\u3046\u5927\u898f\u6a21\u306aCSV\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\uff085GB\uff09\u306e\u51e6\u7406\u7d4c\u9a13"},"content":{"rendered":"\t\t
\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

Requirements<\/span><\/h3>
  • Allow users to upload CSV files (up to 5GB).\u00a0<\/span>
    <\/span><\/li>
  • Validate the data and log all error rows:<\/span>
    <\/span>
    • Columns exceeding 200 characters.<\/span>
      <\/span><\/li>
    • Presence of disallowed special characters.<\/span>
      <\/span><\/li>
    • A set of columns must be unique (no duplicates within the file and compared to the database).<\/span>
      <\/span><\/li><\/ul><\/li>
    • Return to the user:<\/span>
      <\/span>
      • The rows with errors.<\/span>
        <\/span><\/li>
      • Type of error.<\/span>
        <\/span><\/li>
      • The problematic value.<\/span><\/li><\/ul><\/li><\/ul>

        Overall Solution<\/strong><\/h2>

        The processing flow is divided into three main phases:<\/span><\/p>

        1. User Uploads File to S3<\/strong><\/h3>
        • Frontend uploads the file to S3 using a pre-signed URL or S3 SDK.<\/span>
          <\/span><\/li>
        • Once upload is successful, trigger the validation process via EventBridge or by sending a request to the backend.<\/span>
          <\/span><\/li><\/ul>

          2. Batch Processing: Data Validation<\/strong><\/h3>

          The validation process is broken into multiple steps to optimize performance and reduce timeout\/memory leak risks.<\/span><\/p>

          a. Create a Temporary Table in the Database<\/strong><\/h4>
          • Create a temp table mirroring the CSV structure.<\/span><\/li>
          • Add extra columns to log errors: <\/span>column_1_length<\/span>, <\/span>column_1_regex<\/span>, <\/span>is_error<\/span>, <\/span>line_number<\/span>, etc.<\/span><\/li><\/ul>

            b. Load File from S3 into the Temp Table<\/strong><\/h4>
            • Use <\/span>LOAD DATA LOCAL INFILE<\/span> to import the file directly into the database (if S3 is mounted to local).<\/span>
              <\/span><\/li><\/ul>
              • Optionally preprocess to remove newline characters (<\/span>\\r\\n<\/span>) embedded in values.<\/span>
                <\/span><\/li>
              • Temporarily disable indexes to speed up data loading, then re-enable them.<\/span><\/li><\/ul>

                LOAD<\/span> DATA<\/span> LOCAL<\/span> INFILE<\/span> ‘\/path_to_file.csv’<\/span>
                <\/span>INTO<\/span> TABLE<\/span> temp_table<\/span>
                <\/span>FIELDS<\/span> TERMINATED<\/span> BY<\/span> ‘,’<\/span>
                <\/span>ENCLOSED<\/span> BY<\/span> ‘”‘<\/span>
                <\/span>LINES<\/span> TERMINATED<\/span> BY<\/span> ‘\\n’<\/span>
                <\/span>(column_1, column_2)<\/span>
                <\/span>SET<\/span>
                <\/span>\u00a0 column_1 = <\/span>REPLACE<\/span>(<\/span>REPLACE<\/span>(column_1, <\/span>‘\\r’<\/span>, <\/span>”<\/span>), <\/span>‘\\n’<\/span>, <\/span>”<\/span>),<\/span>
                <\/span>\u00a0 column_2 = <\/span>REPLACE<\/span>(<\/span>REPLACE<\/span>(column_2, <\/span>‘\\r’<\/span>, <\/span>”<\/span>), <\/span>‘\\n’<\/span>, <\/span>”<\/span>),<\/span>
                <\/span>\u00a0 column_1_length = <\/span>CHAR_LENGTH<\/span>(column_1),<\/span>
                <\/span>\u00a0 column_2_length = <\/span>CHAR_LENGTH<\/span>(column_2),<\/span>
                <\/span>\u00a0 column_1_regex = column_1 REGEXP <\/span>‘[^a-zA-Z0-9+\\-_.:=?&\/]+’<\/span>,<\/span>
                <\/span>\u00a0 is_error = <\/span>IF<\/span> (column_1_length > <\/span>200<\/span> OR<\/span> column_2_length > <\/span>200<\/span> OR<\/span> column_1_regex > <\/span>0<\/span>, <\/span>1<\/span>, <\/span>0<\/span>)<\/span><\/p>

                c. Retrieve Error Rows and Generate an Error Report<\/strong><\/h4>
                • Select rows with is_error = 1, write them to an error file with row number and error description.<\/li><\/ul>

                  d. Check for Duplicates Within the File<\/strong><\/h4>

                  SELECT<\/span> id_key, <\/span>GROUP_CONCAT<\/span>(line_number) <\/span>AS<\/span> duplicated_lines<\/span>
                  <\/span>FROM<\/span> temp_table<\/span>
                  <\/span>GROUP<\/span> BY<\/span> id_key<\/span>
                  <\/span>HAVING<\/span> COUNT<\/span>(*) > <\/span>1<\/span>;<\/span><\/p>

                  e. Check for Duplicates Against the Database<\/strong><\/h4>

                  Compare id_key in temp_table against the main table in your system.<\/span><\/p>

                  3. Insert Valid Data into the Main Database<\/strong>
                  <\/span><\/h3>
                  • Only include rows without errors.<\/span><\/li>
                  • Use INSERT INTO or REPLACE INTO depending on the use case.<\/li>
                  • Optionally add logic for default values, audit fields, etc.<\/span>
                    <\/span><\/li><\/ul>

                    4. Clean Up Temporary Table<\/strong><\/h3>
                    • Drop the temp table after processing to free up resources.<\/span>
                      <\/span><\/li><\/ul>

                      Tips & Best Practices<\/strong><\/h2>
                      • Disable indexes during large data loads, then re-enable afterward (significantly reduces loading time).<\/span>
                        <\/span><\/li>
                      • Ensure the CSV is correctly encoded (UTF-8, without BOM).<\/span>
                        <\/span><\/li>
                      • Avoid line-by-line validation in code \u2014 use SQL for efficient bulk validation.<\/span>
                        <\/span><\/li>
                      • Log all errors in one pass so users can correct them all at once, rather than iteratively.<\/span>
                        <\/span><\/li><\/ul>

                        Conclusion<\/strong><\/h2>

                        Handling large file uploads isn’t just about raising size limits. You need a smart, efficient processing flow that ensures performance and scalability. With this approach, you can handle even larger files if needed.<\/span><\/p>

                        Would you like help implementing a similar solution in code or cloud architecture?<\/span><\/p>

                        Credit: Ton That Bach (Technical Leader in Mynavi TechTus Vietnam)<\/em><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

                        \u73fe\u4ee3\u3067\u306f\u3001\u65c5\u884c\u3092\u542b\u3080\u3042\u3089\u3086\u308b\u6d3b\u52d5\u306b\u304a\u3044\u3066\u3001\u74b0\u5883\u3084\u5730\u57df\u793e\u4f1a\u3078\u306e\u914d\u616e\u3068\u3001\u3088\u308a\u30a8\u30b3\u30d5\u30ec\u30f3\u30c9\u30ea\u30fc\u3067\u3042\u308b\u3053\u3068\u304c\u91cd\u8981\u3067\u3059\u3002<\/p>","protected":false},"author":1,"featured_media":991915,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[75,12],"tags":[107],"class_list":["post-991926","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","category-news","tag-csv"],"acf":[],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t