代码之家 › 专栏 › 技术社区 › Richard Herron

从大型SQLite数据库中删除列时出现问题

sqlite

Richard Herron · 技术社区 · 14 年前

我有一个15gb的SQLite数据库,有40列。我想删除大多数列,使查询更快,数据库更可移植。我找到这个了 guidance ,但无法启动。它似乎挂了,我最终破坏了数据库,不得不重新开始。以下是我使用的脚本:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE dly_backup(DATE INTEGER,
    TICKER TEXT,
    TSYMBOL TEXT,
    VOL INTEGER,
    RET REAL,
    RETX REAL,
    VWRETD REAL,
    VWRETX REAL,
    EWRETD REAL,
    EWRETX REAL,
    SPRTRN REAL
);
INSERT INTO dly_backup SELECT DATE INTEGER,
    TICKER TEXT,
    TSYMBOL TEXT,
    VOL INTEGER,
    RET REAL,
    RETX REAL,
    vwretd REAL,
    vwretx REAL,
    ewretd REAL,
    ewretx REAL,
    sprtrn REAL
    FROM dly;
DROP TABLE dly;
CREATE TABLE dly(DATE INTEGER,
    TICKER TEXT,
    TSYMBOL TEXT,
    VOL INTEGER,
    RET REAL,
    RETX REAL,
    VWRETD REAL,
    VWRETX REAL,
    EWRETD REAL,
    EWRETX REAL,
    SPRTRN REAL
);
INSERT INTO dly SELECT DATE INTEGER,
    TICKER TEXT,
    TSYMBOL TEXT,
    VOL INTEGER,
    RET REAL,
    RETX REAL,
    VWRETD REAL,
    VWRETX REAL,
    EWRETD REAL,
    EWRETX REAL,
    SPRTRN REAL
    FROM dly_backup;
DROP TABLE dly_backup;
COMMIT;

有更好的办法吗?FWIW,我有原始的.csv文件,并使用R中的RSQLite包导入它。

有没有办法只导入.csv文件中的列子集?谢谢!(SQLite新版本)

1 回复 | 直到 14 年前

paxdiablo 14 年前

实际上,我不知道15G对于SQLite来说是否是一个很大的大小-我倾向于使用DMBS,其中15G可以被视为一个配置表:-)

使数据库完全脱机(换句话说,防止任何人连接和/或更改数据) ^*一个
将当前表重命名为备份表。
使用原始名称和简化模式创建新表。
知道数据是好的,因为它来自一个有约束的表,并且您不需要事务(及其相关的开销,因为您是唯一使用数据库的人)。
最后,重新设置索引/触发器/约束,并为业务重新打开数据库。

当然,我们没有这样做,我们有多个冗余的数据库实例,具有故障转移和各种其他出色的功能,比如复制,但是对于这样的小数据库来说,这可能是可行的。

在我看来,您可以简单地将当前表重命名为备份表,而不是第一个副本。备份中仍然有不需要的列并不重要,因为您不会传输这些列,并且最终会删除备份表。尝试一下(并尽量缩小您的交易范围):

begin transaction;
alter table dly rename to dly_backup;
create table dly (
  date integer, ticker text, tsymbol text, vol integer, ret real, retx real,
  vwretd real, vwretx real, ewretd real, ewretx real, sprtrn real);
commit;

begin transaction;
insert into dly (
    date, ticker, tsymbol, vol, ret, retx,
    vwretd, vwretx, ewretd, ewretx, sprtrn
  ) select
    date, ticker, tsymbol, vol, ret, retx,
    vwretd, vwretx, ewretd, ewretx, sprtrn
  from dly_backup;
commit;

这将导致一个事务的大小是您正在尝试的事务的一半。

只有在那时,只有在没有错误的情况下,你会 drop dly_backup drop dly 然后将备份表重新命名为原始表并重试。

另一件你可能想尝试的事情是限制在测试运行中传输的数据,看看它在较小的数据集下是否可以正常运行。使用原始代码,尝试创建 dly_backup where 子句只获取一个ticker符号,如 MSFT 或 IBM ).

不要删除测试运行中的任何表。

我刚刚注意到你的 insert...select 语句,其中您有两个列名键入。我不知道这是否是SQLite的扩展,但我认为这会给我熟悉的其他DBMS带来问题。那是你的错别字吗?