기록 데이터를 저장하는 방법

기록 데이터를 저장하는 방법

---

일부 동료들과 저는 과거 데이터를 저장하는 가장 좋은 방법에 대해 토론했습니다. 현재 일부 시스템의 경우 별도의 테이블을 사용하여 기록 데이터를 저장하고 현재 활성 레코드에 대한 원래 테이블을 유지합니다. 테이블 FOO가 있다고 가정 해 봅시다. 내 시스템에서 모든 활성 레코드는 FOO로 이동하고 모든 기록 레코드는 FOO_Hist로 이동합니다. FOO의 많은 다른 필드는 사용자가 업데이트 할 수 있으므로 모든 항목에 대한 정확한 계정을 업데이트하고 싶습니다. FOO_Hist는 자동 증가 HIST_ID를 제외하고 FOO와 정확히 동일한 필드를 보유합니다. FOO가 업데이트 될 때마다 FOO_Hist에 다음과 유사한 삽입 문을 수행합니다 insert into FOO_HIST select * from FOO where id = @id.

동료는 역사적인 이유로 테이블의 정확한 사본을 가져서는 안되며 역사적인 목적을 나타내는 플래그가있는 활성 레코드에 다른 레코드를 삽입해야하기 때문에 이것이 나쁜 디자인이라고 말합니다.

히스토리 데이터 스토리지를 처리하기위한 표준이 있습니까? 백만 개가 넘는 레코드가있을 수 있다는 점을 고려할 때 내 활성 레코드를 동일한 테이블의 모든 내 레코드로 어지럽히고 싶지는 않습니다 (장기 생각하고 있습니다).

당신이나 당신 회사는 이것을 어떻게 처리합니까?

MS SQL Server 2008을 사용하고 있지만 모든 DBMS에 대한 일반적인 대답을 유지하고 싶습니다.

---

운영 체제 내에서 히스토리 데이터를 직접 지원하면 애플리케이션이 이전보다 훨씬 복잡해집니다. 일반적으로 시스템 내에서 레코드의 히스토리 버전을 조작하기 어려운 요구 사항이 없으면 수행하지 않는 것이 좋습니다.

자세히 살펴보면 내역 데이터에 대한 대부분의 요구 사항은 다음 두 가지 범주 중 하나에 속합니다.

• 감사 로깅 : 감사 테이블을 사용하는 것이 좋습니다. 시스템 데이터 사전에서 메타 데이터를 읽어 감사 로그 테이블 및 트리거를 작성하는 스크립트를 생성하는 도구를 작성하는 것은 매우 쉽습니다. 이 유형의 도구는 대부분의 시스템에 감사 로깅을 개선하는 데 사용할 수 있습니다. 데이터웨어 하우스를 구현하려는 경우 변경된 서브 시스템에이 서브 시스템을 사용할 수도 있습니다 (아래 참조).

• 내역보고 : 과거 상태, '현재 상태'위치 또는 시간에 따른 분석보고에 대한보고. 위에서 설명한 종류의 감사 로깅 테이블을 쿼리하여 간단한 기록보고 요구 사항을 충족 할 수 있습니다. 보다 복잡한 요구 사항이있는 경우 기록을 운영 체제에 직접 통합하려고 시도하는 것보다보고를 위해 데이터 마트를 구현하는 것이 더 경제적 일 수 있습니다.
  
  차원이 느리게 변경되는 것은 기록 상태를 추적하고 쿼리하는 가장 간단한 메커니즘이며, 기록 추적의 대부분을 자동화 할 수 있습니다. 일반 핸들러는 작성하기 어렵지 않습니다. 일반적으로 기록보고는 최신 데이터를 사용할 필요가 없으므로 일괄 새로 고침 메커니즘이 일반적으로 좋습니다. 이를 통해 핵심 및보고 시스템 아키텍처를 비교적 간단하게 유지할 수 있습니다.

요구 사항이이 두 범주 중 하나에 해당되면 운영 체제에 기록 데이터를 저장하지 않는 것이 좋습니다. 히스토리 기능을 다른 서브 시스템으로 분리하면 전체적인 노력이 줄어들고 의도 한 목적에 훨씬 더 잘 작동하는 트랜잭션 및 감사 /보고 데이터베이스를 생성 할 수 있습니다.

---

나는 그것을하는 특별한 표준 방법이 없다고 생각하지만 가능한 방법으로 던질 것이라고 생각했습니다. Oracle과 애플리케이션 데이터 저장을 위해 XML을 사용하는 사내 웹 애플리케이션 프레임 워크에서 작업합니다.

가장 간단한 마스터-디테일 모델을 사용합니다.

예를 들어 마스터 테이블 에는 Widgets종종 ID가 포함되어 있습니다. 종종 시간이 지나도 변하지 않거나 기록이없는 데이터가 포함됩니다.

예를 들어 다음을 Widget_Details포함하는 상세 / 이력 테이블 :

• ID-기본 키 상세 / 이력 ID
• MASTER_ID-예를 들어이 경우 'WIDGET_ID'라고하면 마스터 레코드에 대한 FK입니다.
• START_DATETIME-해당 데이터베이스 행의 시작을 나타내는 타임 스탬프
• END_DATETIME-해당 데이터베이스 행의 끝을 나타내는 타임 스탬프
• STATUS_CONTROL-단일 문자 열이 행의 상태를 나타냅니다. 'C'는 현재, NULL 또는 'A'는 기록 / 아카이브됨을 나타냅니다. END_DATETIME에 색인을 생성 할 수 없으므로 NULL 만 사용합니다.
• CREATED_BY_WUA_ID-행을 생성 한 계정의 ID를 저장합니다
• XMLDATA-실제 데이터를 저장

따라서 기본적으로 엔터티는 마스터에 1 개의 행과 세부 사항에 1 개의 행을 갖는 것으로 시작합니다. NULL 종료 날짜와 STATUS_CONTROL이 'C'인 세부 사항입니다. 업데이트가 발생하면 현재 행이 현재 시간의 END_DATETIME을 갖도록 업데이트되고 status_control이 NULL (또는 선호하는 경우 'A')로 설정됩니다. 세부 사항 테이블에 새 행이 작성되며, 여전히 동일한 마스터에 링크되어 있으며 status_control 'C', 업데이트하는 개인의 ID 및 XMLDATA 열에 저장된 새 데이터가 있습니다.

이것이 우리의 역사적 모델의 기초입니다. Create / Update 로직은 Oracle PL / SQL 패키지에서 처리되므로 현재 ID, 사용자 ID 및 새 XML 데이터를 함수에 전달하고 내부적으로 행의 모든 ​​업데이트 / 삽입을 수행하여 히스토리 모델에서이를 표시합니다. . 시작 및 종료 시간은 테이블의 해당 행이 활성화 된시기를 나타냅니다.

스토리지는 저렴하며 일반적으로 데이터를 삭제하지 않으며 감사 추적을 유지하는 것을 선호합니다. 이를 통해 주어진 시간에 데이터가 어떻게 보이는지 확인할 수 있습니다. status_control = 'C'를 인덱싱하거나 View를 사용하면 어수선하게 문제가되지 않습니다. 분명히 쿼리는 항상 최신 버전 (NULL end_datetime 및 status_control = 'C')의 레코드를 사용해야합니다.

---

나는 당신이 접근하는 것이 맞다고 생각합니다. 히스토리 테이블은 인덱스가없는 기본 테이블의 사본이어야합니다. 테이블에 업데이트 시간 소인도 있는지 확인하십시오.

다른 방법을 빨리 시도하면 문제가 발생합니다.

• 유지 관리 오버 헤드
• 선택에 더 많은 플래그
• 쿼리 속도 저하
• 테이블, 인덱스의 성장

---

에서 SQL 서버 2016 이상 ,라는 새로운 기능이 임시 테이블 의 목표는이 문제 해결하는 것을 개발자로부터 최소한의 노력은 . 임시 테이블의 개념은 CDC (Change Data Capture)와 유사하지만 임시 테이블이 CDC를 사용하는 경우 수동으로 수행해야하는 대부분의 작업을 추상화했다는 차이점이 있습니다.

---

Change data capture: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017

It is supported in SQL Server 2008 R2, it might have been supported in SQL Server 2008.

---

this question is rather old but people still working on this issue. so if you are using oracle you might be interested in oracle flashback: http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm

---

You could just partition the tables no?

"Partitioned Table and Index Strategies Using SQL Server 2008 When a database table grows in size to the hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes. Just the sheer size of the table causes such operations to take much longer. Even the data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical. The Microsoft SQL Server 2008 database software provides table partitioning to make such operations more manageable."

---

The real question is do you need to use historical data and active data together for reporting? If so keep them in one table, partition and create a view for active records to use in active queries. If you only need to look at them occasionally (to research leagal issues or some such) then put them in a separate table.

---

Another option is to archive the operational data on a [daily|hourly|whatever] basis. Most database engines support the extraction of the data into an archive.

Basically, the idea is to create a scheduled Windows or CRON job that

1. determines the current tables in the operational database
2. selects all data from every table into a CSV or XML file
3. compresses the exported data to a ZIP file, preferably with the timestamp of the generation in the file name for easier archiving.

Many SQL database engines come with a tool that can be used for this purpose. For example, when using MySQL on Linux, the following command can be used in a CRON job to schedule the extraction:

mysqldump --all-databases --xml --lock-tables=false -ppassword | gzip -c | cat > /media/bak/servername-$(date +%Y-%m-%d)-mysql.xml.gz

---

I Know this old post but Just wanted to add few points. The standard for such problems is what works best for the situation. understanding the need for such storage, and potential use of the historical/audit/change tracking data is very importat.

Audit (security purpose) : Use a common table for all your auditable tables. define structure to store column name , before value and after value fields.

Archive/Historical: for cases like tracking previous address , phone number etc. creating a separate table FOO_HIST is better if you your active transaction table schema does not change significantly in the future(if your history table has to have the same structure). if you anticipate table normalization , datatype change addition/removal of columns, store your historical data in xml format . define a table with the following columns (ID,Date, Schema Version, XMLData). this will easily handle schema changes . but you have to deal with xml and that could introduce a level of complication for data retrieval .

---

You can use MSSQL Server Auditing feature. From version SQL Server 2012 you will find this feature in all editions:

http://technet.microsoft.com/en-us/library/cc280386.aspx

---

You can create a materialized/indexed views on the table. Based on your requirement you can do full or partial update of the views. Please see this to create mview and log. How to create materialized views in SQL Server?

---

Just wanted to add an option that I started using because I use Azure SQL and the multiple table thing was way too cumbersome for me. I added an insert/update/delete trigger on my table and then converted the before/after change to json using the "FOR JSON AUTO" feature.

 SET @beforeJson = (SELECT * FROM DELETED FOR JSON AUTO)
SET @afterJson = (SELECT * FROM INSERTED FOR JSON AUTO)

That returns a JSON representation fo the record before/after the change. I then store those values in a history table with a timestamp of when the change occurred (I also store the ID for current record of concern). Using the serialization process, I can control how data is backfilled in the case of changes to schema.

I learned about this from this link here

참고URL : https://stackoverflow.com/questions/3874199/how-to-store-historical-data