Skip to content

Instantly share code, notes, and snippets.

@paschott

paschott/Slack.sql

Created Jan 19, 2019
Embed
What would you like to do?
Creates SQL objects for Slack analysis and adjusts the usernames in the message text column
--Create table to store posts
CREATE TABLE [dbo].[Posts](
[Username] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[Subtype] [nvarchar](255) NULL,
[TS] [nvarchar](255) NULL,
[Channel] [nvarchar](255) NOT NULL,
[MessageText] [nvarchar](max) NULL,
[MessageDate] [datetime2](7) NULL,
[PostID] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)
)
GO
--Create table to store users for more meaningful text analysis
CREATE TABLE Users(UserID NVARCHAR(50), UserName NVARCHAR(100), RealName NVARCHAR(255))
GO
--Import the users separately
--expects the users.json in the given folder
DECLARE @JSON NVARCHAR(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET(BULK 'C:\SlackExport\users.json', SINGLE_CLOB) AS oro
INSERT dbo.Users ( UserID, UserName, RealName )
SELECT DISTINCT id, name, real_name
FROM OPENJSON(@JSON)
WITH (id nvarchar(50), name nvarchar(100), real_name nvarchar(255)) AS oj
--Create a Full-Text index on the MessageText table to allow for faster update to replace user id w/ user name
CREATE FULLTEXT CATALOG [Messages]
WITH ACCENT_SENSITIVITY = ON
GO
CREATE FULLTEXT INDEX ON [dbo].[Posts] (
MessageText Language 1033
)
KEY INDEX PK_Posts
ON [Messages]
WITH CHANGE_TRACKING AUTO
GO
/*
Populate the Posts table using the Powershell script
*/
--Run the following after populating the Posts table to replace the Slack UserID w/ the User Name
DECLARE UserCursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT UserId, UserName
FROM dbo.Users AS u
DECLARE @UserID NVARCHAR(50), @UserName NVARCHAR(100)
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @UserID, @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Posts SET MessageText = REPLACE(MessageText, @UserID, @UserName)
WHERE CONTAINS(MessageText, @UserID)
FETCH NEXT FROM UserCursor INTO @UserID, @UserName
END
CLOSE UserCursor
DEALLOCATE UserCursor
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment